my dates come in as "Apr 1 2008 12:00AM" and i am trying to get the date and time separate from each other.
i tried using 'subfield' and a date# function, but when the date changes to "Apr 31 2008 1:00AM", the character spaces and placement changes and it returns incorrect dates.
Goal is to get the date into 'YYYY-MM-DD' format, and the time into 'HH:MM:SS' format, as new fields, separated from each other.
let me know what i am missing. thanks!
this expression shows data and time in textbox
=date(date#('Apr 1 2008 12:00AM','MMM dd YYYY hh:mmTT'))
& '-' & time(date#('Apr 1 2008 12:00AM','MMM dd YYYY hh:mmTT'))
maybe you can take the relevant part for your desired expression
@Anonymous thanks for the solution. it does not work when inputted the field name as compared to a typed up date. i tried using the chr(39) to get the quotation marks in but that did not work.
i have attached a sample. thanks!
I suggest you reading about Interpretation function Date# and about Formatting functions Date, Time:
There are the result:
Be careful of using date formatting alone. the Date(Date#()) construct does not remove the time component from the date values, it simply hides them, so you can still get multiple distinct values of a single date. You need to explicitly remove the time component like this:
=Date(Floor(Timestamp#(postdateofposition, 'MMM D YYYY h:mmtt')), 'YYYY/MM/DD') =Time(Frac(Timestamp#(postdateofposition, 'MMM D YYYY h:mmtt')), 'HH:MM:SS')