Timesheet Calculation

[blueriver]

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

 

[Eduardo]

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

 

[Daryl S]

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.

 

[Teethless mama]

=MOD(E9-D9,1)*24

Format cell as General

 

[John]

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

 

[John]

I should have said that you can format as time only and it will show just the
time.
HTH
John

 

[blueriver]

THANK YOU ALL!!!!

=MOD(E9-D9,1)*24

Format cell as General

 

[Teethless mama]

You're Welcome!

THANK YOU ALL!!!!

 

[WallyWallWhackr]

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

http://office.microsoft.com/en-us/templates/TC300083091033.aspx

or if that link fails find the task tracking log on this page. It allows
for time increment selection, and it tallies the data separate from the
main data entry sheet/print job sheet/page.

http://office.microsoft.com/en-us/templates/profile.aspx?id=ecf5ad5a-9584-4787-bb16-70f61ad52e0f