Formula Date Time Will not Accept Null Condition

Question

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!

MarkShnierYou · Accepted Answer

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.

DavidLeech · Answer

Fixed it - thank you!

Sign up

Login to the community