Empty Date gives 1970

Viewing 5 reply threads
  • Author
  • October 23, 2020 at 10:51 AM #42373

    John Scoble


    I have 3 date fields in a “Books Read” form: Date 1st Read, Date 2nd Read, and Date 3rd read. Often, the 2nd and 3rd fields will be blank, because the book has only been read once. I have put together a calculated field, which provides the year(s) in which any book has been read. However, if the 2nd and/or 3rd field is blank, the calculated field returns “1970” as the date read in each of the 2nd and 3rd cases, instead of nothing.

    Attached are screen prints, showing an example where the 2nd and 3rd field are blank, but 1970 is returned in each case. Also attached is a screen print of the calculation formula.

    Help! Is this a bug? How can I eliminate the 1970?

    Thanks, John

    You must be logged in to view attached files.
    October 23, 2020 at 11:42 AM #42377


    Hi John,

    You’ll want to add in a check to see if the field is empty or not and then return perhaps the empty string if so.

    IFEMPTY(Date Field; ""; DATE(Date Field; "yyyy"))

    October 25, 2020 at 6:15 AM #42401

    John Scoble

    Thanks, Brendan, that worked.

    However, if the field is empty, why does the DATE function return 1970?


    October 25, 2020 at 9:34 AM #42402

    Sam Moffatt

    The Unix epoch is January 1st, 1970. Dates in a number of systems are stored as relative to that date, either as seconds (Unix compatible systems including MacOS and Linux) or as milliseconds (Javascript). Zeroth time if you will is that date.

    October 29, 2020 at 3:24 AM #42439

    John Scoble

    Thanks, Sam. I guess there’s no way round that.


    October 30, 2020 at 8:52 AM #42454

    Sam Moffatt

    Not really, not for anything that uses these epochs.

    If you’re comfortable with the terminal app you can run date -r 0 to see what zero seconds since the epoch translates to in your local time zone:

    $ date -r 0
    Wed 31 Dec 1969 16:00:00 PST

    I’m in Pacific TZ so 8 hours behind UTC making it 4pm on the 31st of December 1969.

    You can also use date +%s to print the seconds since epoch:

    $ date +%s
Viewing 5 reply threads

You must be logged in to reply to this topic.