USA Text Date to UK Text Date

[rsutton1981]

Hi,

Part of a software that the company uses exports dates in USA text format. How do I convert this to a UK text date.

Ie 1st August 2017 is 42743 in USA but 42948 in the UK.

I have hundreds of dates across multiple sheets (dates are always in G18:10000 and H18:10000) so I need some VBA code to process.

I have an additional problem with this query. Sometimes there is not a date in column G (a quirk with the accounting software). in this case need the o in text being replaced with the same date in Column H.

Any help will be appreciated

 

[Stiuart_W]

Well. Firstly 1/8/2017 (UK) and 8/1/2017 are the same date code (42948).
When Excel displays a date it does it based on the preferences set on your computer (ie UK or US format). But you can change the date format in individual cells or ranges. Try this and you'll see the display changing.

If your data is in TEXT format then that is NOT a date format. What you should do is convert it to a date using some string extraction. Use the formula =DATE(Year, month day)

If you want some code you'll need to detail exactly how the dates are formatted. Ie is it "8/1/17", "08/01/2017" etc

 

[rsutton1981]

Hi Stuiart,

I have found the error. The accounting software exports the date as a text line not date. I will try text to number formatting to see if it works.

 

[FormR]

Part of a software that the company uses exports dates in USA text format. How do I convert this to a UK text date.

Hi, try this on one column and see it successfully converts your dates:

1. Select the single column and choose "Text to Columns" from the data tab.
2. Check Delimited and click next.
3. Uncheck all delimiters and click next.
4. Choose Date and select MDY from the dropdown and click finish.

If this works for your single column we can help you with some code to apply if to multiple columns across multiple sheets.

 

[rsutton1981]

All works

 

[FormR]

Good to hear