I want excel to work out the number of hours worked if I enter, say, 08:00 in one cell and 17:00 in another. Is there a simple formula I can use so that excel gives me an answer of 9 hours?
Will it also work with 'odd' times, i.e. 08:37 to 17:06?
Appreciate any ideas
Barry
Barry Mongan
Replies (4)
Please login or register to join the discussion.
All in the formatting
Simply set the formatting of the input and output cells as time (click format on the toolbar, then select cells, number tab, then time from the list of formats). You even get the choice of 12 or 24 hour input.
The formula is then a simple subtraction of end time from start time.
Time question
Use this formula: =F31-C31-(E31-D31)
where F31 is end time, c31 is start time.
E31 is end of lunch break, D31 is start of lunch break.
Use it it time format to get an answer of say 07:00:00 where HH:MM:SS.
Then convert this to decimal in the adjacent cell with: =HOUR(H31)+MINUTE(H31)/60
Hope this makes sense.
Martin
Have a look at
the examples on Chip Pearsons Excel site
http://www.cpearson.com/excel/datetime.htm#AddingTimes
I find this a very useful source of info for a lot of Excel matters