time formula


G

Guest

I am trying to calculate in and out time for a time sheet spreadsheet.
Example: 9:00 am (in time), 5:00 pm (out time). I devised a formula:

=IF(D7<C7,((C7-D7)*24)+((C7+D7)*24)*2,(D7-C7)*24)

It works for all senarios except if you begin with PM and end with AM.
Example:
5:00 pm (in time), 1:00 am (out time)

Can anyone help me!!
 
Ad

Advertisements

M

mmohon

I've done something similar for Surgery in and out time for my
hospital. Our times never cross a day though.

Understand that time is saved as a faction of a day: a date/time raw
looks like 1000.0001
everything to the left of the decimal is date info, everything to the
right is time info. 1.0 is one day 1.5 is 1 day 12 hours etc

You could add an if statement like.....if ( end time < start time, end
time + 1 ) this adds 24 hours, so 7 am end time raw data would be 31
hours, minus 7pm start time would be 19 hours....so 31 hours minus 19
hours = 12 hours

hope that helps
 
R

Ragdyer

Start time in A1,
End time in B1,

*Either* of these should work:

=B1-A1+(B1<A1)*24

=MOD(B1-A1,1)*24
 
H

Harald Staff

=(D7-C7+(D7<C7))*24
assuming nobody works more than 23 hours 59 minutes.

HTH. Best wishes Harald
 
Ad

Advertisements

H

Harald Staff

Glad to hear that, thanks for the feedback. In case someone should ask,
+(D7<C7)
adds 24 hours to the end time if it's earlier than start time.

Best wishes Harald
 
Ad

Advertisements


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

Top