Time to Number Format

Question

Hello,&nbsp;&nbsp;I'm needing a time format such as 18:34 to be in a number only format (so, 18:34 = 1834, 04:59 = 459, 01:10 = 110 etc)&nbsp;&nbsp;Does anyone know the formulae to do this?&nbsp;Thank you.&nbsp;

sergeibaklan · Answer

dillanpatel&nbsp;
If that's only to change the format, not time value, you may apply custom number format like

dillanpatel · Answer

SergeiBaklan&nbsp;&nbsp;Okay, how would I change it so it can be a number? If I change it to text, it converts it to a decimal that is irrelevant.&nbsp;

owenprice · Answer

dillanpatel&nbsp;&nbsp;The number it is converting it to isn't really irrelevant. Excel uses a decimal number to represent the number of days relative to a fixed date.&nbsp;The decimal part is the fraction of 1 that is represented by the current time of day. So, if it is 5AM, then the decimal part will be equivalent to 5/24.&nbsp;If you want to convert your times to a numeric value, then consider what you hope to do with the number after you have it. If you want to do any kind of mathematical operation, then I recommend leaving it in the datevalue format.&nbsp;If this is just a display issue, then SergeiBaklan 's answer is ideal and you should accept his answeras the best solution.&nbsp;If you still want to convert to a number, then use the format suggested above in the TEXT function, then convert to a number using NUMBERVALUE:&nbsp;=NUMBERVALUE(TEXT(A5,"hmm"))&nbsp;&nbsp;

sergeibaklan · Answer

dillanpatel&nbsp;
It is a number, we only changed the format. One hour in Excel is equal 1/24, thus 04:59 is equal to&nbsp;0.207638888888889. That is the value of it, you may apply General format and check.&nbsp;
Applied format is only makes for this number more human friendly representation, 04:59 or 459, whaever.

dillanpatel · Answer

Thank youSergeiBaklan&nbsp;&nbsp;I wasn't aware that the decimal represented that so I learnt something new today.&nbsp;Thank you for your help and the format concept did help me achieve what I needed.&nbsp;

Forum Discussion

Time to Number Format

6 Replies

Resources