In this article, we are going to see how to convert any Dates in Spreadsheets using Python.
Used file:
This file comprises a single column entitled ‘Date’ and stores random dates of 2021 in some different forms of format.
Approach:
- We’ll begin by importing the pandas library.
- Let’s have a look at the code that converts the dates.
sample_dates[“Date”] = pd.to_datetime(sample_dates[“Date”]).dt.strftime(“%Y-%m-%d”)
- To perform tasks involving date and time, we must first transform the column into a datetime data type, which this section of the code will do:
sample_dates[“Date”] = pd.to_datetime(sample_dates[“Date”])
- Then we use the dt and strftime methods with a value of “%Y-%m-%d” to inform Python how to format the date. Example here we used is “%Y-%m-%” where %Y is the full year, %m is the month with 2 digits and %d is the date with 2 digits.
Example 1: Convert any Dates in Spreadsheets
Python3
# This code converts any dates in spreadsheet import pandas as pd # Read the file and specify which column is the date sample_dates = pd.read_excel("sample_dates.xlsx") # Export output with dates converted to YYYY-MM-DD sample_dates["Date"] = pd.to_datetime( sample_dates["Date"]).dt.strftime("%Y-%m-%d") sample_dates.to_excel("sample_dates_formated.xlsx") |
Output:
Other well-known formats:
Example date – Saturday, 18 December, 2021, 7:00 PM
- “%A, %B %d” -> “Saturday, December 18”
- “%d-%b-%y” -> “18-Dec-21”
- “%d/%m/%Y” -> “18/12/2021”
- “%b %d, %Y” -> “Dec 18, 2021”
| Directive | Meaning | Example |
|---|---|---|
| %a | Weekday as locale’s abbreviated name |
Sun, Mon,…..,Sat(en_US); So, Mo,…..,Sa(de_DE) |
| %A | Weekday as locale full name | Sunday, Monday, ….., Saturday |
| %w | Weekday as a decimal number, where 0 is Sunday and 6 is Saturday | 0, 1, 2, 3……,6 |
| %d | Day of the month as a zero-padded decimal number | 01,02,….31 |
Example 2:
We’re going to use the same dataset that we used in the last example.
Format - "%d %b, %Y" -> "18 December, 2021"
Here we will use ‘%#d’ to remove the padding of zero from the day, i.e. 08 to 8. This will not do padding of zero if the date is in single digit. We can use ‘%-d’ on Linux.
Python3
# This code converts any dates in spreadsheet import pandas as pd # Read the file and specify which column is # the date sample_dates = pd.read_excel("sample_dates.xlsx") # Export output with dates converted to # "D MMMM, YYYY" sample_dates["Date"] = pd.to_datetime( sample_dates["Date"]).dt.strftime("%#d %B, %Y") sample_dates.to_excel("sample_dates_formated.xlsx") |
Output:
Example 3:
Here we will use different format
Format - "%B %d, %Y" -> "December 18, 2021"
Python3
# This code converts any dates in spreadsheet import pandas as pd # Read the file and specify which column is # the date sample_dates = pd.read_excel("sample_dates.xlsx") # Export output with dates converted to "MMMM D, # YYYY" sample_dates["Date"] = pd.to_datetime( sample_dates["Date"]).dt.strftime("%B %d, %Y") sample_dates.to_excel("sample_dates_formated.xlsx") |
Output:
