STEM

How to convert a standard date format <-> Excel date serial number

You may already know that all date type values in Excel are converted to a serial number in Excel. This is an effective way to store date because not only do the digits take less space, it can be calculated upon for all sorts of date maths such as finding difference in days, months, years, additions, subtractions of date/time, etc. In this post, I’ll show the full code on how to convert a standard date format to an Excel serial number, and vice versa.

So what’s the algorithm to convert to Excel serial number? It’s quite simple. January 1, 1900 is serial number 1, and it increments by 1 for each day since, so we need to just find the difference in days between a given date since January 1, 1900. So, for example, July 7, 2024 would be serial number 45480.

In order to demonstrate this, I wrote the following Python script that takes today’s date (‘today’ is defined as your region’s today wherever you’re located), and converts it to a 4-digit serial number. Then it does the conversion in opposite direction by taking the serial number and converting back to a standard date format, which is is YYYY-MM-DD in Python, and then I extract the year, month, and day so I can reformat it whatever way I wish. The two dates (today’s date converted from serial number, which was converted from today’s date) should be exactly the same.

To run the script from this page, click below. The output will be shown the next pane.

I hope this was informational. Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top
+