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.
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"))
Thanks, Brendan, that worked.
However, if the field is empty, why does the DATE function return 1970?
John
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.
Thanks, Sam. I guess there’s no way round that.
John
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
.
