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.
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.
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.
Does “=B4-A4″ not work?
it did not work on google spreadsheet.
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
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.
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!
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.