convert number to date format

I have an Excel file with a Time column formatted as a general number (e.g.
830, 1102, 805). I need to convert these numbers to a time format like 8:30,
11:02, and 8:05.

I've tried =TEXT(a2,"h:mm"); however, it only returns a zero. These data
will be imported into a time formatted field in Access and later used to
determine the difference between schedule delivery time versus actual
delivery time.

Any assistance on how to convert this field would be very helpful. Thanks!

A1=1436

in B1:

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0)

B1 ==> 14:36

Format cell as hh:mm

HTH

"Lesley" wrote:

> I have an Excel file with a Time column formatted as a general number (e.g.
> 830, 1102, 805). I need to convert these numbers to a time format like 8:30,
> 11:02, and 8:05.
>
> I've tried =TEXT(a2,"h:mm"); however, it only returns a zero. These data
> will be imported into a time formatted field in Access and later used to
> determine the difference between schedule delivery time versus actual
> delivery time.
>
> Any assistance on how to convert this field would be very helpful. Thanks!

Lesley

Use

=TIMEVALUE(IF(LEN(A3)=3,LEFT(A3,1)&":"&RIGHT(A3,2),LEFT(A3,2)&":"&RIGHT(A3,2)))

And format as time

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Lesley" <Lesley@discussions.microsoft.com> wrote in message
news:5210C1A4-7308-418A-8BA9-4CDF924A0410@microsoft.com...
>I have an Excel file with a Time column formatted as a general number (e.g.
> 830, 1102, 805). I need to convert these numbers to a time format like
> 8:30,
> 11:02, and 8:05.
>
> I've tried =TEXT(a2,"h:mm"); however, it only returns a zero. These data
> will be imported into a time formatted field in Access and later used to
> determine the difference between schedule delivery time versus actual
> delivery time.
>
> Any assistance on how to convert this field would be very helpful.
> Thanks!

hi Lesley

try it:

=if(len(a2)<4,time(left(a2,1),right(a2,2),0),time(left(a2,2),right(a2,2),0))

hth
regards from Brazil
Marcelo

"Lesley" escreveu:

> I have an Excel file with a Time column formatted as a general number (e.g.
> 830, 1102, 805). I need to convert these numbers to a time format like 8:30,
> 11:02, and 8:05.
>
> I've tried =TEXT(a2,"h:mm"); however, it only returns a zero. These data
> will be imported into a time formatted field in Access and later used to
> determine the difference between schedule delivery time versus actual
> delivery time.
>
> Any assistance on how to convert this field would be very helpful. Thanks!

830 turns to 8 hours 30 minutes?
=--TEXT(A1,"00\:00\:\0\0")
Format as time.

Just in case....
830 turns to 8 minutes 30 seconds?
=--TEXT(A1,"00\:00\:00")
still format as time


Lesley wrote:
>
> I have an Excel file with a Time column formatted as a general number (e.g.
> 830, 1102, 805). I need to convert these numbers to a time format like 8:30,
> 11:02, and 8:05.
>
> I've tried =TEXT(a2,"h:mm"); however, it only returns a zero. These data
> will be imported into a time formatted field in Access and later used to
> determine the difference between schedule delivery time versus actual
> delivery time.
>
> Any assistance on how to convert this field would be very helpful. Thanks!

--

Dave Peterson