If you’ve ever dealt with time series data in spreadsheets, you must have noticed that Excel internally uses unique serial numbers for date values. In this Q-TIP (stands for Quick-Tip), I’ll show you the internal calculation of how it’s done…date to serial number, back from serial number to date using formulas instead of using the GUI interface.
Here’s the formula Excel uses:
Serial# = (number of days between the date and January 1, 1900) + 1
January 1, 1900 is considered as day 1 and assigned the serial number 1. Finally, 1 is added to find the difference and convert to a serial number. This is reversible meaning, given a serial number, Excel can easily convert to any date format.
The formula is simple enough but we can also convert without using Number formatting in GUI by Excel’s built-in formula EDATE(). It returns a serial number.
So how to we find the difference between two dates? We can simply use subtraction or use DATEDIF() function. The following table shows examples of where an input date (yellow cells) and formatted by the above formula to serial numbers (blue cells). The GUI Formatted columns shows the output using GUI.
Now to reverse the process, i.e. to convert a serial number to date using formula, we can use TEXT(serialNumber, formatString). The formatString arugment allows us to display the formatted date string in any format we want. Example below shows “mm/dd/yyyy” format.

* Home tab->Number group: General
** Home tab->Number group: Date
But all these do not work with very old years, such as pre-1900!
None of the WEEKDAY, TEXT, DATEDIF functions work correctly on years prior to 1900. However, if we offset the year in those cases by adding 2000 to it, and use the new offset year value, they will work fine! 2000 is a good number because it covers repeating leap years thereby making month, weekday name, weekday numbers be accurate for our purpose.
The offsetting could be done manually during entry. For example, for a date 2/2/1732 the new offset date would be entered as 2/2/3732. For 11/1/2000, the date would be entered as 11/1/4000. And so on. The following examples show what happens without offset, and with offset. After offset, we can get weekday number, weekday name, months, and differences using all Excel Date formulas: K to M cols. The offset dates are in G, H cols.

We can verify the name of the week or day of the week by going to: https://www.timeanddate.com/calendar/?year=3732&country=1 or, by month: https://www.timeanddate.com/calendar/monthly.html?year=3732&month=2&country=1
So, why not build a calendar link for each of the year? Using any of the offset dates from above, we can use TEXT() to get the years, month number, and create the URL for online calendar. The following table shows the really old years inputs, then offset, and then eventually we build a URL to create online calendars for each.

The links dynamically created for the above dates are as follows:
http://www.timeanddate.com/calendar/monthly.html?year=3760&month=2&country=1http://www.timeanddate.com/calendar/monthly.html?year=3902&month=2&country=1http://www.timeanddate.com/calendar/monthly.html?year=3894&month=2&country=1http://www.timeanddate.com/calendar/monthly.html?year=3790&month=2&country=1http://www.timeanddate.com/calendar/monthly.html?year=3966&month=2&country=1http://www.timeanddate.com/calendar/monthly.html?year=3766&month=2&country=1http://www.timeanddate.com/calendar/monthly.html?year=4020&month=11&country=1
The years will show as the offset years in the calendars, but remember, we did that to ensure that we are able to get the weekday numbers, weekday names even those ancient times, do parsing, and calculations (such as Age) on them. To create the URLs, I used CONCAT() nested within HYPERLINK() making sure the URL Format is as follows: https://www.timeanddate.com/calendar/monthly.html?year=NNNN&month=N&country=1
Note that the accuracy of weekdays can be off for years prior to 1760 (for those we have to offset by a different number and I’ll leave that exercise for the readers). I hope this was useful to you all.
Have you given my intenet radio station ‘Flying Salmon Radio‘ a listen yet? You can listen to FlyingSalmon Radio broadcast 24/7 all over the world. Click the Play button below to listen now. Learn more about the station. You can point your browser or any media player that supports opening an internet playlist to: https://zeno.fm/radio/flyingsalmon/
You can also find my radio on Radio Garden mobile app or site (best FREE radio app and service in my opinion). Direct browser link to my Radio Garden stream.
▛Interested in creating programmable, cool electronic gadgets? Give my newest book on Arduino a try: Hello Arduino! 
▟