Pre-1900 dates as serial date

building reports
1

There’s some fields in our database that have dates prior to 01/01/1900: date of birth and address start & end dates (01/01/1800). These dates fail in an Excel export. Address start and end dates aren’t a problem; I just replace them with 01/01/1900 and Excel is happy. Date of birth, on the other hand, is more of an issue. It’s been suggested to convert the date to a text field and export that, but then we run into issues with date handling in Excel. Ideally, what I want to be able to do is to convert the date to the Unix serial date, but I can’t find a function that does that…

Any ideas?

2

Unix date 0 is 1 Jan, 1970 - it’s not possible to represent an earlier date than this in unixtime

MS Excel CANNOT handle dates before 1900.

So if you need to show a date before 1 Jan 1900 you need to put it into a text field. And yes that means you cannot do any date math on it.

3

Here is one article where a guy found a work around.

But if you care about leap days and durations this won’t help you because the calendar is a nightmare

If you convert to Julian Dates you move the zero back to a much “safer” and probably out of range date of 1 January 4713 BC.

Even MS gives you a caveat (They note that 1 Jan has not always been the start of the new year - that used to be a day, 25 March, called “Lady Day” ). So your formula will have to be smart*

What is your underlying database? Many DB have functions that can do the conversion from a date field to a text field. You’ll have to embed raw SQL in your report.

Do you have access to the DB such that you could create a view that does this conversion for you?

Here is a valid Julian Date conversion tool online so you can check your formulas

Note that “22010” is not a Julian date in 2022. It’s in the 47th century BC - specifically “4653 B.C. April 5” (they note that it was a Wednesday)

Do not use tools like

to convert your Julian dates, they are NOT Julian Dates, they are serialized/textified Gregorian dates.

*Note the Tom Scott video above.

5

Unfortunately the database is a third party product (a social care case management system). We can’t add our own functionality to the BO universe either - it’s locked down plus we don’t have the in-house expertise to do so (and to maintain any local changes).

Fortunately, it isn’t generally an issue for current or recent cases; it’s only when we’re looking at historic records for data quality purposes or with a view to archiving records. I’ll just have to make sure to create alternate ‘date’ fields…