Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need some help changing the below date format to 04/02/2021.
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')
You can use: Date(YourDateField, 'MM/DD/YYYY')
JG
Hi JG,
I tried that is not working. I even tired Date# and then converted to date format still won't work
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
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
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
JG,
The Mid function does give me the date, but the date# and Date function won't work.
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')
Thank you so much, JG.