EXEL TIME CALCUALTIONS

  peug417 23:04 06 Mar 05
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 05

Custom format

[h]:mm:ss

  peug417 23:12 06 Mar 05

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 05

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 05

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

  Simsy 08:25 07 Mar 05

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 05

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.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Fresh New Fonts of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced