excel converts date/time value to exponential value

Hi,

I have a CSV file that was exported by a database. However, when I
import this csv to excel, I see that the date/time value seems to have
changed to an exponential value. For example,
20060602084140 is converted to 2.00606E+13.

1. Why is this happening?
2. Can I convert 20060602084140 to a regular date / time value?

Thanks in advance,
-V

It's because the value is a VERY long value, without date seperators -
import it as text, then use date serial with MID to reformat it (in a
holding column)
<[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a CSV file that was exported by a database. However, when I
> import this csv to excel, I see that the date/time value seems to have
> changed to an exponential value. For example,
> 20060602084140 is converted to 2.00606E+13.
>
> 1. Why is this happening?
> 2. Can I convert 20060602084140 to a regular date / time value?
>
> Thanks in advance,
> -V
>

You can convert your number to a date/time with the formula:

=date(left(a1,4),mid(a1,5,2),mid(a1,7,2))+time(mid(a1,9,2),mid(a1,11,2),right(a1,2))

--
Regards,
Fred


<[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a CSV file that was exported by a database. However, when I
> import this csv to excel, I see that the date/time value seems to have
> changed to an exponential value. For example,
> 20060602084140 is converted to 2.00606E+13.
>
> 1. Why is this happening?
> 2. Can I convert 20060602084140 to a regular date / time value?
>
> Thanks in advance,
> -V
>

One more if you can insert a helper column and use a formula:

=--TEXT(A1,"0000\/00\/00 00\:00\:00.000")

Format that cell as mm/dd/yyyy hh:mm:ss.000
(or whatever date format you want)

[email protected] wrote:
>
> Hi,
>
> I have a CSV file that was exported by a database. However, when I
> import this csv to excel, I see that the date/time value seems to have
> changed to an exponential value. For example,
> 20060602084140 is converted to 2.00606E+13.
>
> 1. Why is this happening?
> 2. Can I convert 20060602084140 to a regular date / time value?
>
> Thanks in advance,
> -V

--

Dave Peterson