Timesheet Calculation

B

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
 
D

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.
 
J

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
 
J

John

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top