Time formatting issue

**Tony Vargo** · 06-16-2009, 06:22 AM

Hi guys.

I'm really struggling with this, even though it sounds simple!

I'm working on a flexitime sheet for my department (I've attached a sample sheet). Each month the remaining hours, if any, roll over to the next month, and it also adds on two hours for that month. It all works fine except when I try and put it into hours/minutes format.

Any ideas or help would be appreciated.

Thanks all,

Tony

**DonkeyOte** · 06-16-2009, 06:30 AM

Time in XL is decimal - ie 12 hours = 0.5, as far as XL is concerned 2 equates to 2 days hence formatting as time will display 00:00 (2 days and 0 time remainder) or if you used [hh]:mm it would read as 48:00 (2 * 24 hours)

You also have added problem that by default XL can't display actual time values if they are negative ... unless a) running 1904 date sytem (not advised) b) all values are negative ... and so in truth you may in fact be better off continuing to work in your present format.

**Jbentley** · 06-16-2009, 06:43 AM

DonkeyOte's right.
As far as XL is concerned Time only moves foward.

If you're looking to simply display the time (in adjacent cells for instance) you could use the following.

Please Login or Register  to view this content.

Where B3 is one of the values you want to display in time format.
The result will be text, and unusable in formulae.

Please note: the formula above was adapted from a post by JBeaucaire on the same topics - if you've found this useful credit goes to the above.

**DonkeyOte** · 06-16-2009, 06:46 AM

The formula could be IF less in truth

=REPT("-",B3<0)&TEXT(ABS(B3/24),"H:MM")

If you were prepared to accept it you could apply a close-ish custom format of 00.00 to the source values such that 2 would display as 02.00 etc (remainder would be in format of 0.5 for 30 mins however)