Sunday, September 15, 2024

Q-TIP: Excel’s handling of dates

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