How to convert dates that "hardcoded" when imported into excel

[bsl6323ling]

Hi,

I have this perpetuate problem. When I import information into excel the dates get "messed" up.

1. In the original program the date format is DD/MM/YY but when its imported some dates retain the dd/mm/yy format some change to mm/dd/yy. I have tried to use the format cells function but it wont help.

2. Some times it gets imported in as a numeric. see attached.

[TABLE="width: 142"]
<colgroup><col><col></colgroup><tbody>[TABLE="width: 225"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Date before import
[/TD]
[TD]Date after import[/TD]
[/TR]
[TR]
[TD]20/01/2017[/TD]
[TD]20/01/2017[/TD]
[/TR]
[TR]
[TD]07/02/2017[/TD]
[TD="align: right"]42918[/TD]
[/TR]
[TR]
[TD]13/02/2017[/TD]
[TD]13/02/2017[/TD]
[/TR]
[TR]
[TD]20/03/2017[/TD]
[TD]20/03/2017[/TD]
[/TR]
</tbody>[/TABLE]
[TR]

[/TR]
[TR]
[TD]Is there a formuala to convert the imported information into a common format?
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[FDibbins]

Hi, welcome to the board

You cannot attach files here, so you will need to show some sample data. Depending on what your import looks like, the conversion could be as simple as using the =DATEVALUE() function

 

[bsl6323ling]

Thank you Ford!

My apologies, trying to figure out how to create a table in the posting space.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date before import
[/TD]
[TD]Date after import
[/TD]
[/TR]
[TR]
[TD]20/01/2017
[/TD]
[TD]20/01/2017
[/TD]
[/TR]
[TR]
[TD]07/02/2017
[/TD]
[TD]42918
[/TD]
[/TR]
[TR]
[TD]13/02/2017
[/TD]
[TD]13/02/2017
[/TD]
[/TR]
[TR]
[TD]20/03/2017
[/TD]
[TD]20/03/2017
[/TD]
[/TR]
[TR]
[TD]05/03/2017
[/TD]
[TD]03/05/2017
[/TD]
[/TR]
</tbody>[/TABLE]

I have highlighted the relevant cells in red.

Thank you,
Ling

 

[FDibbins]

The cells in read are actually real dates (just need to be formatted as a date), but the rest are really just text looking like dates.

If your system date is mm/dd/yy, then try this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]Date before import[/td][td]Date after import[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]20/01/2017[/td][td]20/01/2017[/td][td]

1/20/2017​

[/td][/tr]

[tr][td]

3​

[/td][td]7/2/2017[/td][td]7/2/2017[/td][td]

7/2/2017​

[/td][/tr]

[tr][td]

4​

[/td][td]13/02/2017[/td][td]13/02/2017[/td][td]

2/13/2017​

[/td][/tr]

[tr][td]

5​

[/td][td]20/03/2017[/td][td]20/03/2017[/td][td]

3/20/2017​

[/td][/tr]

[tr][td]

6​

[/td][td]5/3/2017[/td][td]3/5/2017[/td][td]

5/3/2017​

[/td][/tr]

[tr][td]

7​

[/td][td]2/03/2017[/td][td][/td][td]

3/2/2017​

[/td][/tr]
[/table]
C2=IFERROR(--(MID(A2,FIND("/",A2)+1,2)&"/"&LEFT(A2,FIND("/",A2)-1)&"/"&RIGHT(A2,4)),A2)
copied down