Date format

[22strider]

Hello Friends,

Is there a way for me to change date format from 'day/month/year' to 'month/day/year'?

Sample data in my file is as following:

Current value: 2/12/2019
Intended value: 12/2/2019

There are about 300 values in the column.

Regards,
Rajesh

 

[Joe4]

What are your local settings set at to display your dates (in what format)?
Are your values currently entered as dates or text?

If you aren't sure, you can use the ISNUMBER function on them.
For an entry in cell A1, use =ISNUMBER(A1)
If that returns TRUE, it is a Date entry.
If that returns FALSE, it is a Text entry.

 

[22strider]

Hello Joe4,

Just tested; unfortunately I am getting mix of TRUE and FALSE.

This data in Excel workbook was extracted from another application. And I am trying to have MS Access read this data. Access is reading the data with following 2 faults:

1) Access complaining for dates such as '18/12/2019'; because it detects 18 to be invalid month
2) Access is not complaining for dates such as '2/12/2019'; but reading it incorrectly as data from month of February (rather than December).

Regards,
Rajesh

 

[Joe4]

Try applying this formula on the column of dates, and then choosing the mm/dd/yyyy date format.
=DATEVALUE(IF(ISNUMBER(A1),TEXT(A1,"dd/mm/yyyy"),TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",100)),100,100))&"/"&TRIM(LEFT(SUBSTITUTE(A1,"/",REPT(" ",100)),3))&RIGHT(A1,5)))

I believe this should convert all those dates to valid date values that Access will like.

 

[22strider]

After another check entries that have values before first dash less than or equal to 12 (e.g. 4/12/2019) are returning 'TRUE' for ISNUMBER test. But values such as '16/12/2019' (16 being greater than 12) are returning FALSE.

 

[Joe4]

After another check entries that have values before first dash less than or equal to 12 (e.g. 4/12/2019) are returning 'TRUE' for ISNUMBER test. But values such as '16/12/2019' (16 being greater than 12) are returning FALSE.

My formula handles both those situations just fine. Did you try it?

 

[22strider]

Thank you very much. The formula converted values to numbers like '48301' that I was able to reformat to dates like 12/2/2019. This will work for me.

Thanks again.

 

[Joe4]

Thank you very much. The formula converted values to numbers like '48301' that I was able to reformat to dates like 12/2/2019. This will work for me.

Thanks again.

That is because that is how Excel actually stores dates. Dates are actually stored as the number of days since January 0, 1900 (it is an Integer value), and time is just a fraction of one day.
So dates are just these Integers with a Custom date format.

To easily see this, enter any date value in Excel, and then change the format to General, and you will see the date as Excel does, as a number like 48301.