peug417 23:04 06 Mar 2005

Hi all
I have two coulumns one with the a start time the second with a finish time. A Third colums has number of breaks (IN TIME) and a final column which works out the total hours having deducting the break. My problem is the formatting. if i use the hh.mm format 06.30 becomes 07:12 and i can't find a suitable custom format.. any ideas would be appreciated

  VoG II 23:06 06 Mar 2005

Custom format


  peug417 23:12 06 Mar 2005

Hi there Vog
haven't spoken to you for a while now, being conversing direct with Whisperer. That format displays 151:12:00

  Happy Soul 23:47 06 Mar 2005


The chosen format should be Time - 37:30:55

A1 enter start 08:00

A2 enter finish 16:00

A3 enter break 0:30

A4 enter =Sum(a2-a1-a3)

To make things less complicated if the time starts one day and finishes the next, say 22:00 to 06:00, add 24 to the finish time ie 30:00 and it will work OK.

I believe in simplicity. Hope this helps.

  THE TERMINATOR 00:42 07 Mar 2005

That's what I call "a thinking mans" man. I like your style....TT

  Simsy 08:25 07 Mar 2005

just by way of explanation;

when you entered 6.3 and got 7:12 the reason is that you used a full stop instead of a colon.

Because of this Excel thought you were entering a number, not a time. The way Excel treats time, (each whole day is "1" in mathematical terms), it thought you were entering 6.3 days.

Because you had formated the cell to show hours and minutes, (hh:mm), it discarded the whole days and just showed the 0.3 of a day, i.e. 7 hours and 12 minutes.

If you had formatted the cell using the square brakets round the hours, [hh:mm], it would have kept the 6 full days and shown them as hours, resulting in 151:12 being displayed, (151 being 6 days @ 24 hrs plus the 7 hours 12 mins from the .3)

The secret when entering times is to use the colon as the seperator between hrs, mins and seconds. This can be done automatically using a macro, but there can be drawbacks to doing this.

I hope this helps.



  peug417 20:47 17 Mar 2005

Thanks to you all for your input especially simsy for pointing out the obvious which i couldnt see for looking the final code i opted for was:::


thanks once again

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

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

No need to scan sketches into your computer with Moleskine's new smart pen

WWDC history: Apple's product launches since 2005

Comment importer des contacts d’un iPhone à un autre iPhone ?