Time to Number Format

[dillanpatel]

Hello, 

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) 

Does anyone know the formulae to do this?

Thank you. 

[#2]

@dillanpatel 

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

[#3]

@Sergei Baklan 

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. 

[#4]

@dillanpatel 

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.

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.

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.

If this is just a display issue, then @Sergei Baklan 's answer is ideal and you should accept his answer

as the best solution.

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:

=NUMBERVALUE(TEXT(A5,"hmm"))

[#5]

@dillanpatel 

It is a number, we only changed the format. One hour in Excel is equal 1/24, thus 04:59 is equal to 0.207638888888889. That is the value of it, you may apply General format and check. 

Applied format is only makes for this number more human friendly representation, 04:59 or 459, whaever.

[#6]

Thank you@Sergei Baklan 

I wasn't aware that the decimal represented that so I learnt something new today.

Thank you for your help and the format concept did help me achieve what I needed. 

[#7]

@dillanpatel , you are welcome