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
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! 
▟