
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 01, 2020 01:12 PM
I have four fields:
-
Unix Timestamp - a single line text field that contains the unix timestamp
-
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’)
-
Date - where I wish to extract just the date from the Date & Time field
-
Time - where I wish to extract just the time from the Date & Time field
How do I get numbers 3 and 4 to work?
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 01, 2020 01:54 PM
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'
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 01, 2020 01:54 PM
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'
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 01, 2020 02:06 PM
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:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 01, 2020 02:40 PM
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.
