Convert scientific format to Date format

[Valeriya]

Hello,
I've got scheduled exported CSV file from ERP system which needs to be converted to the default MS Excel format. I've been specialising in MS Access only so don't have much experience in Excel.Trying to come up with the formula/macro (some kind hard coded automatic way)which would convert general number format to Excel Date (mm/dd/yyyy)amd trancate the Time Part(Date from & Date To Columns) and convert Item Number column to Number):

[b]Location	Date From	Date To	Location From	Item Number	%
1031014000   2.00609E+13	2.00609E+13	P1038	8.85074E+11	25
1031014000	2.00609E+13	2.00609E+13	P1035	8.85074E+11	100
1031014000	2.00609E+13	2.0061E+13	P1038	8.85074E+11	75
1031014000	2.00609E+13	2.0061E+13	P1035	8.85074E+11	50
1031014000	2.0061E+13	2.00611E+13	P1038	8.85074E+11	100

[/b]

Thank you in advance!

Valeriya

 

[JerryKlmns]

Valeriya

If your date field is in B2 then this formula should convert it to a valid date without time.
=DATE(LEFT(B2;4);MID(B2;5;2);MID(B2;7;2))
About the Item No cant fingure it out. It has to do with your importing though...
Excel wont hold a numeric value where the integer part is greater than 15 digits (currency variable). During import define it as text.

 

[SkipVought]

Hi,

You need to fully understand DATE/TIME in Excel.

Why do Dates and Times seem to be so much trouble? faq68-5827

If you look at your first value, it is...
[tt]
20060900000000
[/tt]
What you have is a STRING of numeric digits. It appears that it might be in the form of
[tt]
yyyymmddhhmmss
[/tt]
However, there is no DAY value in the value I copied and pasted, probably due to lost precision in the copy 'n' paste process.

Skip,

_[red][/red]