Formula Date Time Will not Accept Null Condition
Hi all -
I have a dataset importing into QuickBase but the dates arrive in EPOCH time (milliseconds since 1/1/1970). These import into the field dtmDateCompleted, a numeric field.
I am trying to create a formula date/time field that will convert the EPOCH date into the correct format for reporting. If the record does not have a date completed (dtmDateCompleted is NULL), then this formula Date/Time field should remain blank. I have the conversion for populated fields functioning correctly; however, for dtmDateCompleted fields that are null, it is listing 1/1/1970 instead of leaving the field blank.
TL;DR, the below formula is not accepting the first IsNull() condition in a formula date/time field.
This is the formula I have so far:
If(IsNull([dtmDateCompleted]), null,
If(not IsNull([dtmDateCompleted]),
(ToTimestamp(ToDate("1/1/1970"), ToTimeOfDay("00:00:00")) + Seconds([dtmDateCompleted]/1000))))
Thank you!
Numeric fields typically default to treating blank as zero. Go to the field properties for [dtmDateCompleted] and deselelct that option. Then blank / empty will be treated as null.