Formula to calculate number of hours in google spreadsheet

8 06 2009

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.

About these ads

Actions

Information

12 responses

23 07 2009
Molly Parker

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.

13 08 2010
Toxic Chinese Drywall

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.

26 08 2010
stgn

Does “=B4-A4″ not work?

27 08 2010
Prajwala

it did not work on google spreadsheet.

30 08 2010
stgn

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

31 08 2010
Prajwala

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.

28 03 2012
Yehoshua

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!

30 03 2012
Prajwala

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.

16 04 2014
Mike Bailey

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)

20 08 2012
Lamar Romero

That freakin rocked. Exactly what I was looking for…thank you Prajwala

8 09 2012
Fernando Hamasaki (@Prodis)

Thank your for information.

15 11 2013
Rob

Bingo your formula worked! Thanks for your help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

%d bloggers like this: