Empty Date gives 1970

[John Scoble]

Hi

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

Attachments:

You must be logged in to view attached files.

[Brendan]

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"))

[John Scoble]

Thanks, Brendan, that worked.

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

John

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

[John Scoble]

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

John

[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
1604073139

[Author]

Posts