Change dates from uk format to us format

[Fredo]

I wont to import a excel into my db, the problem is that the excel has the dates in uk format, dd/mm/yyy, hh,mm, ss and my db date format is us format short date mm/dd/yyyy. How can do fix that?

[Welshgasman]

Your DB should be in the same format.
The only issue is if you use the dates in string with VBA.

if you create a query in design view and add a date criteria, even if the the date is 01/12/2023 1st Dec 2023, Access will convert that in SQLview to 12/01/2023
I used to used this for queries where I built the criteria in VBA

Public Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Needed for dates in queries as Access expects USA format.

I used to use it as
debug.Print Format(date,strcjetdate)
#12/03/2023#

Other option is in yyyy-mm-dd format which is unambiguous.

[journeyman]

The best method I've found is to convert your dates to a long value, then you can format it however you want.

e.g. UK 04/12/2023 = 45264.00

Dim dDate as DateDim lngdate As Long


    lngdate = CLng(CDate("04/12/2023")) ' converts to 45264
   dDate = Format(lngdate , "mm/dd/yyyy")

However, US dates - pure hellish nightmarish pain - don't do it! I just want to burst into tears thinking about it!

Reconsider!

[June7]

Best I can suggest is to save Excel as CSV (assuming dates will retain UK structure) then import/link CSV and force date field to be treated as text. Then in Access run INSERT SELECT using text manipulation in query to parse date string and rebuild in U.S. style to add records into destination table.

[Welshgasman]

Have you even tried importing and specifying it is a date field?

[June7]

Welsh, I tested import/link with Excel and CSV - date of 13/1/2023 just errors (#Num!) when imported as date/time type. External data wizard does not offer opportunity to specify field type with Excel import/link - can with text file.

[Welshgasman]

Well I know they are just numbers as well in Excel. As I have copied and pasted, and have had to format the destination cell as a date, as the initial paste, just produced a number.

[June7]

Probably works because your Excel data and your system are both UK.

My system is U.S. and just doesn't handle the 13/1/2023. Excel is not reading this as a valid date and treats it as text even when other values in same column are recognized as date.

[Welshgasman]

Probably works because your Excel data and your system are both UK.

My system is U.S. and just doesn't handle the 13/1/2023. Excel is not reading this as a valid date and treats it as text even when other values in same column are recognized as date.

Using my old diabetes workbook.
Column A has my dates in UK format.
Column O has a value of =A2 and copied down the sheet.
Then I formatted column O as US short date format.

As I mentioned, the dates are stored as numbers, so the dates are still correct, even in US format.

If this fails, then just unformat the dates and format them as numbers, with no decimals.

[June7]

I tried formatting every which way I could imagine. Value typed in as 13/1/2023 just isn't handled. Won't even convert to number. OP says dates are in UK structure so that is what I tested.

[Welshgasman]

Well no. You would put the date in in your region format. Them change it to UK format. The numeric number is going to be the same, just like Access stores the date as a number.

It is just a format after all.

[June7]

And I get impression that OP is receiving data that was entered as UK date and import is failing because of that. That is what I attempted to emulate.

We need to hear from Fredo on whether formatting has made a difference.

[Welshgasman]

We also need to know the method of importing, as I am sure an import spec would solve this. Or even change the format to US as I did. The value is not changing, just the format.

[June7]

AFAIK, cannot build import spec with Excel, can with CSV.

I assumed someone is typing into this sheet in UK structure: 13/1/2023. If their system is actually storing value for 1/13/2023, then yes, makes sense OP changing format should work. Apparently, I cannot replicate this and would need the actual sheet causing issue in order to evaluate.

[Welshgasman]

I can send you that diabetes workbook, if you like?
PM me if you do.

In fact no need for PM. Here it is.
I had to add a .txt extension to get it uploaded. Just remove that extra extension.

The import wizard allows me to specify type of fields or skip, though it recognises both the UK and USA columns fine by itself.

EDit: I think you should have enough on the Daily sheet, but if not there is more data on the Old Daily sheet. You would need to add the extra column with the =A2 formula and format of USA date yourself in that case.