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