# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Convert number to time

## NCSemon

Hi all,

I cannot figure out how to convert a number to time format (eg: 1340 to 13:40).  I've tried following the directions here, but there seems to be an error in the IF statement (no logic check?).  I can't seem to fix it.  Any help would be greatly appreciated.

Thanks in advance,
Nick

----------


## Marcelo

Hi,

use:

=time(left(a1,2),rigth(a1,2),0)

hth
regards from Brazil
Marcelo

"NCSemon" escreveu:

>
> Hi all,
>
> I cannot figure out how to convert a number to time format (eg: 1340 to
> 13:40).  I've tried following the directions 'here'
> (http://www.exceltip.com/st/Convertin...Value/99.html),
> but there seems to be an error in the IF statement (no logic check?).  I
> can't seem to fix it.  Any help would be greatly appreciated.
>
> Thanks in advance,
> Nick
>
>
> --
> NCSemon
> ------------------------------------------------------------------------
> NCSemon's Profile: http://www.excelforum.com/member.php...o&userid=35999
> View this thread: http://www.excelforum.com/showthread...hreadid=557869
>
>

----------


## Toppers

Nick,
There is an error in the example you looked at (a "<" missing in
the IF statement ...A31000 should be A3<1000).

The following works OK with cell formatted as hh:mm

=IF(A1<1000,TIMEVALUE(LEFT(A1,1)&":"&RIGHT(A1,2)),TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)))

HTH

"NCSemon" wrote:

>
> Hi all,
>
> I cannot figure out how to convert a number to time format (eg: 1340 to
> 13:40).  I've tried following the directions 'here'
> (http://www.exceltip.com/st/Convertin...Value/99.html),
> but there seems to be an error in the IF statement (no logic check?).  I
> can't seem to fix it.  Any help would be greatly appreciated.
>
> Thanks in advance,
> Nick
>
>
> --
> NCSemon
> ------------------------------------------------------------------------
> NCSemon's Profile: http://www.excelforum.com/member.php...o&userid=35999
> View this thread: http://www.excelforum.com/showthread...hreadid=557869
>
>

----------


## Dave Peterson

Some more:
13 minutes 40 seconds returned from 1340???
=--TEXT(A1,"00\:00\:00")

13 hours 40 minutes 0 seconds???
=--TEXT(A1,"00\:00\:\0\0")

Format either as time.


NCSemon wrote:
>
> Hi all,
>
> I cannot figure out how to convert a number to time format (eg: 1340 to
> 13:40).  I've tried following the directions 'here'
> (http://www.exceltip.com/st/Convertin...Value/99.html),
> but there seems to be an error in the IF statement (no logic check?).  I
> can't seem to fix it.  Any help would be greatly appreciated.
>
> Thanks in advance,
> Nick
>
> --
> NCSemon
> ------------------------------------------------------------------------
> NCSemon's Profile: http://www.excelforum.com/member.php...o&userid=35999
> View this thread: http://www.excelforum.com/showthread...hreadid=557869

--

Dave Peterson

----------


## NCSemon

Thanks, all!

Dave: Your suggestion (for hh/mm/ss) worked great.

Marcelo and Toppers: Had some problems with yours returning odd values for some times in the 00 hour range, but Dave's equation took care of it.  I'll provide you examples if you're terribly interested, but I appreciate your help either way.

Thanks again!

----------


## bplumhoff@gmail.com

Hi Nick,

There seems to be a typo in your link: A31000 should be A3<1000, I
think.

But I suggest to take:
=TIMEVALUE(LEFT(TEXT(A1,"#000"),LEN(TEXT(A1,"#000"))-2)&":"&RIGHT(TEXT(A1,"#000"),2))

HTH,
Bernd

----------


## basi

Here is my addition to the solution of the problem. First I suggest you to divide the time value by 100 so it must be looking like 12.34 or 0.56 or 23.00 or 3.45 or something like that. I just use that kind of format in my tables. Then you could use my formula:




```
Please Login or Register  to view this content.
```


Or if you don't have a spare column or raw for the above you can add dividing in formula on every place where the box address is shown (in this case A1) and it should be A1/100 and change 10 to 1000.

I hope that will help you all.

----------


## martindwilson

i'll pop back in 2016 to see if there are any more answers

----------


## sctlippert

I spent about 30 hours or more analyzing this.  Military time does not convert easily due to the 00:00 hours.  You can't use Len right-2 or left-2 because that's assuming there are four digits in the number while 00:00 hours shows only as 1 zero, thereby being seen as only 1 digit. If the formulas sent to you work (as I didn't try the last two) then you don't need my example, otherwise, email me and I'll send you my worksheet.

Scott

sctlippert@gmail.com

----------


## martindwilson

ah only a year more this time...scott this thread is nearly 7 years old.

----------


## Duncan7670

This is how I convert numbers posing as time to real time. Bear in mind that if entering times in transport related applications, entering, say 727 rather than 7:27 ,saves time especially if you are entering hundreds or indeed thousands of times into a spreadsheet. Enter the data and then convert afterwards I say.

cell 1	Enter time in the first cell as a simple time e.g 1000 for 10:00, 745 for 07:45 or 2345 for 23:45.
cell 2	Extract hour by dividing by 100
cell 3	Which gives a full decimal e.g. 725 becomes 7.25
cell 4	Extract hour only using "INT" function so 7.25 becomes 7
cell 5	Extract decimal minutes by subtracting full hour from simple time
cell 6	Convert hours to time of day by dividing hour by 24
cell 7	Convert minutes to time of day by dividing minutes by (24*60) [1440]
cell 8	Add hours and minutes which are now both time of day 
cell 9	Watch as your time comes out as a "proper" time which can be used by databases and spreadsheets in proper calculations using time functions.

So, going across it looks like this:

725	7		7.250	7.000		25.000	    0.291666667	0.017361111		0.309027778	07:25

725	=B4/100	=C4	       =INT(D4)   	=B4-(E4*100)		=E4/24	=F4/(24*60)	=G4+H4	=I4

Afterwards you can copy and "paste values" to fix the times or hide the working columns.

Happy to send a spreadsheet to anyone who would find it useful

Duncan Stewart

----------


## chiko_236

I found your answer verry interesting, but when I look for something, short answer is better.
So, I get my self to symplify :


1300	13:00	                                                        1:00:00 PM
1300	=((LEFT(A1,ABS(LEN(A1)-2)))/24)+((ABS(RIGHT(A1,2)))/(24*60))	=IF(B1>12,B1-12,B1)











> This is how I convert numbers posing as time to real time. Bear in mind that if entering times in transport related applications, entering, say 727 rather than 7:27 ,saves time especially if you are entering hundreds or indeed thousands of times into a spreadsheet. Enter the data and then convert afterwards I say.
> 
> cell 1	Enter time in the first cell as a simple time e.g 1000 for 10:00, 745 for 07:45 or 2345 for 23:45.
> cell 2	Extract hour by dividing by 100
> cell 3	Which gives a full decimal e.g. 725 becomes 7.25
> cell 4	Extract hour only using "INT" function so 7.25 becomes 7
> cell 5	Extract decimal minutes by subtracting full hour from simple time
> cell 6	Convert hours to time of day by dividing hour by 24
> cell 7	Convert minutes to time of day by dividing minutes by (24*60) [1440]
> ...

----------


## Duncan7670

Agreed - shorter is better. I'll try it out and when I get back fromleave I'll pass acros to collegues who alos neeed this feature.

BTW - does it work with Excel 2003? [Yes, i know ;(( how old that is ...]

Many thanks.

Duncan Stewart

----------


## Grimace

Martin ..... yet still useful  :Cool:

----------


## Tyler.Rabun

Here's a quick formula to convert military number time (i.e. 1231) to any time format of your liking:

=INT(A1/100)/24+(A1-INT(A1/100)*100)/24/60

Where A1 contains the  military number time. Don't forget to change the number format of the column containing the formula to the time format of your preference.

----------

