Forum Discussion
Unusual Time format
I have an unusual time format that I would like to add up the hours. So 0930 is 9hours 30 mins and 0415 is 4hours 15mins etc. I have tried changing the format to time and manually tried h:mm and variation of, I have tried changing it to 9.3 but can't then get it to go 9.5.
Let's say you have a value such a 930 in D2, with custom number format 0000 so that it is displayed as 0930.
You can convert it to a time value using =--TEXT(D2, "00\:00")
Format the cell with this formula as hh:mm. Result: 09:30 as a 'real' time value.
If you prefer decimal hours, use =24*TEXT(D2, "00\:00") and format the cell with the formula as General or as Number. Result: 9.5
If you want to sum a series of values in D2:D10 in one go:
=SUM(--TEXT(D2:D10, "00\:00")) and format as [hh]:mm to allow for total times over 24 hours.
Or =SUM(24*TEXT(D2:D10, "00\:00")) and format as General or as Number.
HansVogelaar thank you, worked a treat. I had managed to get in 4 digits but didn't know how to convert and change to time format correctly
Another function that could be used is DOLLARDE.
= DOLLARDE(custom/100,60)/24Dividing by 100 places the decimal separator between your hours and minutes. The 60 as a 2nd parameter defines the decimal part to be 60ths of an hour rather than the 100th that is standard for decimal numbers. The result is in units of 1 hour so, to convert to Excel time, divide by 24 to give the time as a fraction of a day.
