Prev: Merging cells
Next: SOLVED: Input calculation in specified rows and columns
From: blueriver on 19 Feb 2010 12:53
I am sure this question has been answered before but I am dizzy trying to
sort through all of the other posts when I run a search.

I have a spreadsheet that does some pretty basic time in and time outs. I
have a validation table only allowing time on the quarter of an hour for the
time in and time out. It is formatted in 12:00 AM time.

In this particular instance I cannot get my formula to work when the end
time is 12:00 AM. E.g.:

Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads
=IF(E9>0,(E9-D9)*1440/60,""). This formula returns nothing when the end time
is 12:00 AM. I need it to return 2.00 for the amount of hours.

Any thoughts and Thanks!
-CRM

From: Eduardo on 19 Feb 2010 13:11
Hi,
look into CPearson web

http://www.cpearson.com/excel/overtime.htm

there you will find times examples and the formulas for each calculation
Hope this helps



"blueriver" wrote:

> I am sure this question has been answered before but I am dizzy trying to
> sort through all of the other posts when I run a search.
>
> I have a spreadsheet that does some pretty basic time in and time outs. I
> have a validation table only allowing time on the quarter of an hour for the
> time in and time out. It is formatted in 12:00 AM time.
>
> In this particular instance I cannot get my formula to work when the end
> time is 12:00 AM. E.g.:
>
> Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads
> =IF(E9>0,(E9-D9)*1440/60,""). This formula returns nothing when the end time
> is 12:00 AM. I need it to return 2.00 for the amount of hours.
>
> Any thoughts and Thanks!
> -CRM
>
From: Daryl S on 19 Feb 2010 13:16
CRM -

12:00 AM is midnight, which is 0 time. Depending on how you get your time,
you might just need to test for E9>=0, like this:

=IF(E9>=0,(E9-D9)*1440/60,"")

Check for negative hours (depends on how you have the time). If you get
them, then you will need to add 24 hours to the negative amount.

--
Daryl S


"blueriver" wrote:

> I am sure this question has been answered before but I am dizzy trying to
> sort through all of the other posts when I run a search.
>
> I have a spreadsheet that does some pretty basic time in and time outs. I
> have a validation table only allowing time on the quarter of an hour for the
> time in and time out. It is formatted in 12:00 AM time.
>
> In this particular instance I cannot get my formula to work when the end
> time is 12:00 AM. E.g.:
>
> Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads
> =IF(E9>0,(E9-D9)*1440/60,""). This formula returns nothing when the end time
> is 12:00 AM. I need it to return 2.00 for the amount of hours.
>
> Any thoughts and Thanks!
> -CRM
>
From: Teethless mama on 19 Feb 2010 13:16
=MOD(E9-D9,1)*24

Format cell as General

"blueriver" wrote:

> I am sure this question has been answered before but I am dizzy trying to
> sort through all of the other posts when I run a search.
>
> I have a spreadsheet that does some pretty basic time in and time outs. I
> have a validation table only allowing time on the quarter of an hour for the
> time in and time out. It is formatted in 12:00 AM time.
>
> In this particular instance I cannot get my formula to work when the end
> time is 12:00 AM. E.g.:
>
> Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads
> =IF(E9>0,(E9-D9)*1440/60,""). This formula returns nothing when the end time
> is 12:00 AM. I need it to return 2.00 for the amount of hours.
>
> Any thoughts and Thanks!
> -CRM
>
From: John on 19 Feb 2010 13:20
Hi Blueriver

The problem is that XL does not know that 12 AM is the next day if you put the
date & time in the cell, your formula should work . "Formating as General "
e.g. Time IN 2/19/2010 22:00
Time OUT 2/20/2010 00:00
HTH
John


"blueriver" <blueriver(a)discussions.microsoft.com> wrote in message
news:C384E142-799C-4399-BEF4-B7171F47F278(a)microsoft.com...
>I am sure this question has been answered before but I am dizzy trying to
> sort through all of the other posts when I run a search.
>
> I have a spreadsheet that does some pretty basic time in and time outs. I
> have a validation table only allowing time on the quarter of an hour for the
> time in and time out. It is formatted in 12:00 AM time.
>
> In this particular instance I cannot get my formula to work when the end
> time is 12:00 AM. E.g.:
>
> Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads
> =IF(E9>0,(E9-D9)*1440/60,""). This formula returns nothing when the end time
> is 12:00 AM. I need it to return 2.00 for the amount of hours.
>
> Any thoughts and Thanks!
> -CRM
>

 |  Next  |  Last
Pages: 1 2
Prev: Merging cells
Next: SOLVED: Input calculation in specified rows and columns