Dates: subtracting a day automatically on historical dates

[Elizabeth_King]

Hi there,

I am trying to build a database of historical events, but whenever I input the start date or end date of an event from another century, one day is automatically subtracted from the date for some reason. So, if I put in 12/12/1765, as soon as I leave the cell it transforms into 11/12/1765 and there is apparently no way I can correct this, even if I click on the correct date in the calendar. I don’t have this problem when I input dates from this year or in the future, and I don’t want to add a time field because the time is often not recorded. Is there any way I can fix this?

If anyone could help I would be so grateful!

[Nathalie_Collin]

Hi @Elizabeth_King and Welcome to Airtable Community :smiling_face_with_three_hearts:

When I type that date in, I too get the same results. At first I thought it was a time zone issue but now, it looks like it might have something to do with the various calendars - Gregorian, Julian, etc.

Quote from Wiki

The calendar was developed as a correction to the Julian calendar,[Note 1] shortening the average year by 0.0075 days to stop the drift of the calendar with respect to the equinoxes.[3] To deal with the 10 days’ difference (between calendar and reality) that this drift had already reached, the date was advanced so that 4 October 1582 was followed by 15 October 1582.

If you (or someone else) knows enough about historical dates, time calendars, then perhaps a formula to depict if date is before DateX, than add X number of days/hours.

Alternatively, if you don’t actually need to use that date field for any other formulation now or later on, you could consider changing it to a text field - not the best idea I know.

Sorry I couldn’t be of any more help. This intrigued me though.

UPDATE: (Just because I can’t let things lie :crazy_face: )
FUN FACT: The dates stop adjusting in the year 1883

[Elizabeth_King]

Thanks so much for your response, I also thought it was a time zone issue! I had thought of changing it to a text field but I was hoping to be able to visually map the events later on a timeline, and it’s seriously annoying not to be able to do this! I will try contacting them directly as this is going to be a recurring problem for anyone using this software for historical research.

Thanks again!