# EXEL TIME CALCUALTIONS

peug417 23:04 06 Mar 2005
Locked

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

[h]:mm:ss

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

E.G.

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.

Regards,

Simsy

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:::

=IF(OR(L5=0,K5=0),"",IF(L5<K5,L5+24-K5-M5,L5-K5-M5))

thanks once again

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

Elsewhere on IDG sites

OnePlus 5 review

Stunning new film posters by Hattie Stewart, Joe Cruz & more