Simple calculatulation of time in and out in spreadsheet

[dawnd3]

I have a spreadsheet (excel 2003) to track time on a project. I have a date feild, time in and time out. (time fields formated as hh:mm PM) I want to be able total the time worked in each record but I cannot think of how to do the calculation. If I say [time out]-[time in] it sometimes does not give me a correct result i.e. [2:00 AM] - [10:00 PM]. Please tell me how to total the elapsed time in each record and then how to sum the total the hours worked in the spreadsheet.

Seems like it should be simple, but I am obviously missing something.

Thank you,

Dawn

http://www.calliopeconsulting.net

 

[kpetursson]

Add an if function.

=if(timeIN<timeOUT,timeOUT-timeIN,(1-timeIN)+timeOUT)

or

=(dateOUT+timeOUT)-(dateIN+dateOUT)

the second solution being preferable, as if your "shift" excedes 24hours (on purpose or by accident) this will show it. It the "Shift" is not allowed to excede 24hours this method will allow you to test for that case and flag the error.

hope this helps.

PS. Date and time are just simple number to excel. the date being the number of days since jan 1 1900 (or 1904 depending on your setings). The time is a decimal equivelant of 24 hours (6am=0.25, 6pm=0.75, 9pm=0.875, etc.)

have a good day.


Kevin Petursson

 

[dawnd3]

Hi Kevin, I did the first one and it worked. The problem is that in order for it to show the proper result I had to format the total field as hh:mm. So, let say I am subtracting 11:15 AM from 12:15 PM it will show properly as 1:00 but I don't believe that means "1 hour" to excel, I believe it means "1 oclock". How do I format the total column so that I can get a proper grand total? Right now I should be getting a total of 36:15 hours, but when I total the field formatted as hh:mm I get 12:15 (assumably 1 day and 12 hours and 15 minutes.)

Does this make sense?

Thanks for your help.

Dawn

http://www.calliopeconsulting.net

 

[TonyJollans]

Hi Dawn,

You are correct in saying that what you see really means 1 o'clock to Excel. However, it doesn't matter in this instance.

You are also correct in your interpretation of the result that you see. All that is wrong is your format - if you change it to [highlight][[/highlight]h[highlight]][/highlight]:mm the hours will no longer be converted to days and you should see 36:15 as you ask.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[kpetursson]

do you need the total field formatted in hh:mm??
could you not just format the field as decimal?
it seems to me that to total time is just as likely to be understood as a decimal time.



Kevin Petursson

 

[TonyJollans]

Kevin,

I dare say people can understand time as a decimal but formatting the field that way will not give the right result. Times are held in units of Days, so 1:00 is 1/24 (of a day) and formatting as decimal will give (if my maths is good) 0.042. Most people will not be able to understand that!!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[kpetursson]

True Tony...

You could multiply the total by 24 so as to see a readable decimal time. This may have to be done anyway it this spreadsheet will be used to calculate costs, or wages. Having the time displayed hh:mm may look nice, but try to multiply it be $7.50/hour (without considering what is behind the hh:mm)

Thanks.


Kevin Petursson