Date to text and vice versa in excel

Question

I have seen that excel identifies dates with specific serial numbers. For example :

    09/07/2018 = 43290
    10/07/2018 = 43291

I know that we use the DATEVALUE , VALUE and the TEXT functions to convert between these types.

But what is the logic behind this conversion? why 43290 for 09/07/2018 ?

Also , if I have a list of these dates in the number format in a dataframe (Python), how can I convert this number to the date format?

Similarly with time, I see decimal values in place of a regular time format. What is the logic behind these time conversions?

The following question that has been given in the comments is informative, but does not answer my question of the logic behind the conversion between Date and Text format : convert numerical representation of date (excel format) to python date and time, then split them into two seperate dataframe columns in pandas

DeepSpace · Accepted Answer · 2018-12-27T12:25:17.117

It is simply the number of days (or fraction of days, if talking about date and time) since January 1^st 1900:

The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number of the date 1/1/2008. Remember, though, that your computer's system date setting may cause the results of a DATEVALUE function to vary from this example
...
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

from DATEVALUE docs

if I have a list of these dates in the number format in a dataframe (Python), how can I convert this number to the date format?

Since we know this number represents the number of days since 1/1/1900 it can be easily converted to a date:

from datetime import datetime, timedelta

day_number = 43290

print(datetime(1900, 1, 1) + timedelta(days=day_number - 2))
#  2018-07-09 00:00:00                                   ^ subtracting 2 because 1/1/1900 is
#                                                          "day 1", not "day 0"

However pd.read_excel should be able to handle this automatically.

Yep. This pretty much sums it up. Actually, this data is present in an xlsb file. I since there is no function called read_xlsb, I wrote a function to read it. But the result was that somehow, this date got converted into numerical format so read_excel won't help. However, your solution seems to work well for now. — Babaji, Dec 27 '18 at 12:28
What about time ? For example, `0.516550926` refers to `12:23:50`. How to perform this conversion? And what's the logic behind it? — Babaji, Dec 28 '18 at 03:40

Date to text and vice versa in excel

1 Answers1