The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
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.
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'
)
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'
)
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:
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.