Skip to main content

I have four fields:







  1. Unix Timestamp - a single line text field that contains the unix timestamp







  2. Date & Time - a formula field that converts the unix timestamp into a readable date and time using the following formula: DATEADD(‘1/1/1970’,{Unix Timestamp},‘seconds’)







  3. Date - where I wish to extract just the date from the Date & Time field







  4. Time - where I wish to extract just the time from the Date & Time field







How do I get numbers 3 and 4 to work?

You can use DATETIME_FORMAT for both 3 and 4. It is documented in the formula field reference.



For the time, you will probably also want to use SET_TiMEZONE to set the timezone or you will get the time as GMT time.



date



DATETIME_FORMAT(


  SET_TIMEZONE({Date & Time field}, 'Australia/Sydney'), 


  'M/D/YYYY'


)


time



DATETIME_FORMAT(


  SET_TIMEZONE({Date & Time field}, 'Australia/Sydney'), 


  'h:mm'


)


Note that you can also combine these formulas with formula use in step 2, to eliminate the field in step 2.



DATETIME_FORMAT(


  SET_TIMEZONE(


    DATEADD('1/1/1970',{Unix Timestamp},'seconds'), 


    'Australia/Sydney'


  ), 


  'M/D/YYYY'


)

You can use DATETIME_FORMAT for both 3 and 4. It is documented in the formula field reference.



For the time, you will probably also want to use SET_TiMEZONE to set the timezone or you will get the time as GMT time.



date



DATETIME_FORMAT(


  SET_TIMEZONE({Date & Time field}, 'Australia/Sydney'), 


  'M/D/YYYY'


)


time



DATETIME_FORMAT(


  SET_TIMEZONE({Date & Time field}, 'Australia/Sydney'), 


  'h:mm'


)


Note that you can also combine these formulas with formula use in step 2, to eliminate the field in step 2.



DATETIME_FORMAT(


  SET_TIMEZONE(


    DATEADD('1/1/1970',{Unix Timestamp},'seconds'), 


    'Australia/Sydney'


  ), 


  'M/D/YYYY'


)

Kuovonne to the rescue again! Thanks! The date and time formulas worked perfectly. The last formula didn’t work for me, but I don’t mind having an extra column that I could hide. 🙂

Kuovonne to the rescue again! Thanks! The date and time formulas worked perfectly. The last formula didn’t work for me, but I don’t mind having an extra column that I could hide. 🙂


Some curly quotes snuck into the last formula when I copy/pasted from your earlier post. I edited my post to turn them in straight quotes, so you can try it again.

Reply


Terms & Conditions