Date Format

[has75042]

I need some help changing the below date format to 04/02/2021.

[JuanGerardo]

You can use: Date(YourDateField, 'MM/DD/YYYY')

JG

[has75042]

Hi JG,

I tried that is not working. I even tired Date# and then converted to date format still won't work

[JuanGerardo]

Maybe your field is not a date field but a string field? In that case you will have to do 2 conversions, text --> date --> text. I will also get only the date part and ignore week day and time with Mid() function. Try this:

Date(Date#(Mid(YourDateField, 5, 13), 'MMM DD, YYYY'), 'MM/DD/YYYY')

Or with a fixed value, for testing: 

Date(Date#(Mid('Fri Apr 23, 2020 04:33 PM', 5, 13), 'MMM DD, YYYY'), 'MM/DD/YYYY')

PD.- Do not forget to check your MonthNames variable in the script, as it contains the short names for months that have to correspond to the ones in your texts.

JG

[rwunderlich]

Don't forget to add a Floor() to get only the date part

Date(Floor(Date#(Mid('Fri Apr 23, 2020 04:33 PM', 5, 13), 'MMM DD, YYYY')), 'MM/DD/YYYY')

-Rob

[JuanGerardo]

Thanks Rob, it is a good practice when time component is included in dates (a timestamp), but in this case the Mid() function is excluding the hour and minute, so Floor() is redundant.

JG

[has75042]

JG,

The Mid function does give me the date, but the date# and Date function won't work. 

[JuanGerardo · Accepted solution]

I think this is because I didn't use a comma with the week day (Fri instead of Fri,). You must change the initial position in Mid(), like this:

Date(Date#(Mid(YourDateField, 6, 13), 'MMM DD, YYYY'), 'MM/DD/YYYY')

And: 

Date(Date#(Mid('Fri, Apr 23, 2020 04:33 PM', 6, 13), 'MMM DD, YYYY'), 'MM/DD/YYYY')

[has75042]

Thank you so much, JG.