Excel Time Calc problem

  Terry Brown 21:32 21 Apr 2006

I am trying to find an excel system for the following problem
I need to add up several different times using the system "finish time-start time = hours worked"
Then add up these times to get a total of hours worked.

Start Finish Total Hours
7.00 15.30 8.30
7.15 14.00 6.45
8.00 17.00 9.00
Total ------
Time 24.15

I am using Excel 2003

Many thanks

  VoG II 21:37 21 Apr 2006

To get Excel to treat times as times you have to enter them in this format: 11:16

otherwise Excel will treat them as regular numbers.

If you enter them in Time format, Excel will happily add them up for you. Unfortunately it does not recognise that a Time can be greater than 24 hours. To get around this you need to specify a custom format in the result cell: [h]:mm

  pj123 10:39 22 Apr 2006

I created a Time Sheet in excel for the office staff when I was working, (retired now).

As VoG™ says, it won't go past midnight but you can get round that by putting a finish time of 23:59 and a new start time of 00:01 and then add 2 minutes to the next finish time. eg. start 22:00 finish 23:59. Start 00:01 finish 08:02

It is based on Flexitime but can be amended to read Overtime if necessary.

It runs in 4 week cycles.

You are welcome to a copy if you want to try it. Email me via the envelope.

  Simsy 13:02 22 Apr 2006

is quite correct. I find it helps to think of the word "duration"... there is no such "time" as 25 o'clock, but there is a "duration" of 25 hours. The [] brackets round the hours in the formating makes it a duration instead of a time.

I make a very complex timesheet a while ago, and I would'ne have even started it with the excellent help and advice I got at the following site;
click here

It explains how to deal with this in Excel very thoroughly, including nightshift complications, but clearly.

Good luck,



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Samsung Galaxy S9 review

ManvsMachine and other artists put Apple's iMac Pro to the test using powerful rendering tools

What to expect at Apple's 27 March education event

Comment filmer l’écran d’un iPhone ?