I was keeping my timesheet on google spread sheet. I was just mentioning start time and end time of particular task. My project manager asked me to add number of hours as another column.

When I search for the formula I found this link http://www.google.com/support/forum/p/Google+Docs/thread?tid=071c90492bf64b75&hl=en . In that link I found solution to my problem.

I need to keep start time in one column(ex: A4 with value 10:50:00), end time in another column(ex.B4 with value 13:42:00). I should maintain time in 24 hours format. I have another column called Hours(ex. D4 should have 2.9 hours).

The cell value for Hours column should be difference of time from B4 and A4 in terms of hours. So I used **=ROUND(ABS(B4-A4) * 24, 1)** formula in that cell. I got the exact number of hours I spent. Just drag the column with ‘+’ to affect the same formula for the rest of the cells in the hours column.

### Like this:

Like Loading...

*Related*

Molly Parker(23:51:20) :If you are trying to create a timesheet, check out http://www.calculatehours.com. It has free excel timesheet templates and some excel training on creating your own timesheet.

Toxic Chinese Drywall(03:01:41) :i appreciate enlightening post. I had been happy when i read through your current write-up and now have made a membership into it. You should up-date this content when you’re able to as I would like more information.

stgn(20:55:36) :Does “=B4-A4″ not work?

Prajwala(18:15:45) :it did not work on google spreadsheet.

stgn(15:23:06) :Thanks for your quick reply.

Oh, I think I have misread your blog post. You wanted the result to be 2.9 hours. I wanted the result to be 2:52, meaning 2 hours and 52 minutes. So “=B4-A4″ worked for me. See example in this public Google spreadsheet:

https://spreadsheets.google.com/ccc?key=tUSze2hACyIWy_wpuB_JKZQ&hl=en#gid=0

Prajwala(00:31:43) :Yes, I want hours in 2.9 format. 2:52 minutes format is also good but in our organization we follow 2.9 hours format.

Yehoshua(19:51:38) :Your info on how to calculate time differences was VERY helpful. Thanks!

Now one question: What do I do if somebody started work at 16:00 and finished at 2:00 the next morning? I want the result to be 10 hrs, but it comes out 14hrs…

Thanks Mr. Prajwala!

Prajwala(11:04:16) :If you start work at 16:00 and finished on next day 2:00 am then you have to enter the task info in 2 days.

for the first day 16:00 – 24:00, second day 00:00 to 2:00. Because you can not keep date from 16:00 – 2:00 because in the middle first day ended and second day started. So you need to keep the 2 days.

Mike Bailey(01:39:38) :Actually, we can handle this type of calculation that goes from one day to the next (beyond midnight):

Where:

B = In time (start of day)

C = Out time (start of lunch break)

D = In time (end of lunch break)

E = Out time (end of day)

Regular Hours Formula:

=IF(((($E2-$B2+($E2<$B2))-($D2-$C2+($D28,8,((($E2-$B2+($E2<$B2))-($D2-$C2+($D2<$C2)))*24))

OT Hours Formula (in CA OT begins after 8 hours):

=IF(((($E2-$B2+($E2<$B2))-($D2-$C2+($D28,IF(((($E2-$B2+($E2<$B2))-($D2-$C2+($D24,4,((($E2-$B2+($E2<$B2))-($D2-$C2+($D2<$C2)))*24)-8),0)

DT Hours Formula (in CA DT begins after 12 hours):

=IF(((($E2-$B2+($E2<$B2))-($D2-$C2+($D212,((($E2-$B2+($E2<$B2))-($D2-$C2+($D2<$C2)))*24)-12,0)

Lamar Romero(11:15:17) :That freakin rocked. Exactly what I was looking for…thank you Prajwala

Fernando Hamasaki (@Prodis)(12:14:22) :Thank your for information.

Rob(00:25:16) :Bingo your formula worked! Thanks for your help.