Forum Discussion

DavidLeech's avatar
DavidLeech
Qrew Member
18 days ago

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.

  • 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.