Time to Number Format

%3CLINGO-SUB%20id%3D%22lingo-sub-1518323%22%20slang%3D%22en-US%22%3ETime%20to%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518323%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20needing%20a%20time%20format%20such%20as%2018%3A34%20to%20be%20in%20a%20number%20only%20format%20(so%2C%2018%3A34%20%3D%201834%2C%2004%3A59%20%3D%20459%2C%2001%3A10%20%3D%20110%20etc)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20the%20formulae%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1518323%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1518338%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20to%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711960%22%20target%3D%22_blank%22%3E%40dillanpatel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that's%20only%20to%20change%20the%20format%2C%20not%20time%20value%2C%20you%20may%20apply%20custom%20number%20format%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20445px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205002i4D6300FBC895598E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1518390%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20to%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518390%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOkay%2C%20how%20would%20I%20change%20it%20so%20it%20can%20be%20a%20number%3F%20If%20I%20change%20it%20to%20text%2C%20it%20converts%20it%20to%20a%20decimal%20that%20is%20irrelevant.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1518404%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20to%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518404%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711960%22%20target%3D%22_blank%22%3E%40dillanpatel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20number%20it%20is%20converting%20it%20to%20isn't%20really%20irrelevant.%20Excel%20uses%20a%20decimal%20number%20to%20represent%20the%20number%20of%20days%20relative%20to%20a%20fixed%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20decimal%20part%20is%20the%20fraction%20of%201%20that%20is%20represented%20by%20the%20current%20time%20of%20day.%20So%2C%20if%20it%20is%205AM%2C%20then%20the%20decimal%20part%20will%20be%20equivalent%20to%205%2F24.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20convert%20your%20times%20to%20a%20numeric%20value%2C%20then%20consider%20what%20you%20hope%20to%20do%20with%20the%20number%20after%20you%20have%20it.%20If%20you%20want%20to%20do%20any%20kind%20of%20mathematical%20operation%2C%20then%20I%20recommend%20leaving%20it%20in%20the%20datevalue%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20is%20just%20a%20display%20issue%2C%20then%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20's%20answer%20is%20ideal%20and%20you%20should%20accept%20his%20answer%3C%2FP%3E%3CP%3Eas%20the%20best%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20still%20want%20to%20convert%20to%20a%20number%2C%20then%20use%20the%20format%20suggested%20above%20in%20the%20TEXT%20function%2C%20then%20convert%20to%20a%20number%20using%20NUMBERVALUE%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DNUMBERVALUE(TEXT(A5%2C%22hmm%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1518412%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20to%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518412%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711960%22%20target%3D%22_blank%22%3E%40dillanpatel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20is%20a%20number%2C%20we%20only%20changed%20the%20format.%20One%20hour%20in%20Excel%20is%20equal%201%2F24%2C%20thus%2004%3A59%20is%20equal%20to%26nbsp%3B0.207638888888889.%20That%20is%20the%20value%20of%20it%2C%20you%20may%20apply%20General%20format%20and%20check.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EApplied%20format%20is%20only%20makes%20for%20this%20number%20more%20human%20friendly%20representation%2C%2004%3A59%20or%20459%2C%20whaever.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1518427%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20to%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518427%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wasn't%20aware%20that%20the%20decimal%20represented%20that%20so%20I%20learnt%20something%20new%20today.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%20and%20the%20format%20concept%20did%20help%20me%20achieve%20what%20I%20needed.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1518561%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20to%20Number%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518561%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711960%22%20target%3D%22_blank%22%3E%40dillanpatel%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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. 

6 Replies

@dillanpatel 

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

image.png

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

@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"))

 

 

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

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. 

@dillanpatel , you are welcome