CODEWITHSUNDEEP
pythonexcelpandasdatetime
Camilla
Camilla

Reputation: 131

Convert pandas datetime column to Excel serial date

I have a pandas dataframe with date values, however, I need to convert it from dates to text General format like in Excel, not to date string, in order to match with primary keys values in SQL, which are, unfortunately, reordered in general format. Is it possible to do it Python or the only way to convert this column to general format in Excel?

Here is how the dataframe's column looks like:

   ID         Desired Output
1/1/2022        44562
7/21/2024       45494
1/1/1931        11324

Upvotes: 3

Views: 2160

Answers (2)

RSale
RSale

Reputation: 510

Yes, it's possible. The general format in Excel starts counting the days from the date 1900-1-1.

You can calculate a time delta between the dates in ID and 1900-1-1.

Inspired by this post you could do...

data = pd.DataFrame({'ID': ['1/1/2022','7/21/2024','1/1/1931']})
data['General format'] = (
    pd.to_datetime(data["ID"]) - pd.Timestamp("1900-01-01")
    ).dt.days + 2
print(data)

          ID  General format
0   1/1/2022           44562
1  7/21/2024           45494
2   1/1/1931           11324

The +2 is because:

  1. Excel starts counting from 1 instead of 0
  2. Excel incorrectly considers 1900 as a leap year

Upvotes: 4

tzman
tzman

Reputation: 174

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.
-Microsoft's documentation

So you can just calculate (difference between your date and January 1, 1900) + 1

see How to calculate number of days between two given dates

Upvotes: 0

Related Questions