Separate date and time from timestamp

[Alba_Machado]

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?

[kuovonne · Accepted solution]

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'
)

[Alba_Machado]

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. :slightly_smiling_face:

[kuovonne]

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.