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