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