Unusual Time format

Copper Contributor

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.

3 Replies

Re: Unusual Time format

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.

Re: Unusual Time format

Another function that could be used is DOLLARDE.

``= DOLLARDE(custom/100,60)/24``

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

Re: Unusual Time format

@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