Python Program to Convert any Dates in Spreadsheets

by

In this tutorial, we’ll look at how to use Python to convert any Dates in Spreadsheets.

Let us take an example of demo.xlsx excel spreadsheet that is shown below:

Python Program to Convert any Dates in Spreadsheets

This file contains a single column called ‘Dates,’ which stores random dates from 2020 in various formats.

to_datetime() Method: To conduct date and time operations, we must first convert the column to a datetime data type.

dt.strftime() Method:

Then, using a value of “%Y-%m-%d” we utilise the dt and strftime methods to tell Python how to format the date. The example we used here is “% Y-% m-% “, where % Y is the whole year, % m is the month with two digits, and % d is the day with two digits.

Program to Convert any Dates in Spreadsheets in Python

Example1

Approach:

  • Import pandas module as pd using the import keyword.
  • Read the file and indicate which column contains the Dates using the read_excel() function and store it in a variable.
  • (here the file has only 1 column, hence we didn’t specify Dates column).
  • Convert the Dates column in the above file to YYYY-MM-DD format using the to_datetime,dt.strftime functions.
  • And store the formated dates output into another Excel file using the to_excel() function.
  • The Exit of the Program.

Below is the implementation:

# Import pandas module as pd using the import keyword 
import pandas as pd
  
# Read the file and indicate which column contains the Dates using the read_excel() function
# and store it in a variable
# (here the file has only 1 column, hence we didn't specify Dates column)
demo_file = pd.read_excel("demo.xlsx")
  
# Convert the Dates column in the above file to YYYY-MM-DD format using the to_datetime,dt.strftime functions
# And store the formated dates output into another Excel file using the to_excel() function.
demo_file["Dates"] = pd.to_datetime(
    demo_file["Dates"]).dt.strftime("%Y-%m-%d")
demo_file.to_excel("demo_format.xlsx")

Output:

Python Program to Convert any Dates in Spreadsheets 1

Different date formats:
Example: Monday, 16 May, 2020, 5:00 PM

“%A, %B %d” -> “Monday, May18”
“%d-%b-%y” -> “16-May-20”
“%d/%m/%Y” -> “16/05/2020”
“%b %d, %Y” -> “May 16, 2020”

      Directive            Description              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, Tuesday,….., Saturday
            %w Weekday as a decimal number, where 0 represents Sunday and 6 represents Saturday 0, 1, 2, 3, 4, 5, 6
            %d The month’s day as a zero-padded decimal number 01, 02, 03, …………,31

Example2:

Now let us change the date format as shown below for the same dataset

Format:

 "%d %b, %Y"  --> "16 May, 2020"
# Import pandas module as pd using the import keyword 
import pandas as pd
  
# Read the file and indicate which column contains the Dates using the read_excel() function
# and store it in a variable
# (here the file has only 1 column, hence we didn't specify Dates column)
demo_file = pd.read_excel("demo.xlsx")
  
# Convert the Dates column in the above file to "D MMMM, YYYY" format using the to_datetime,dt.strftime functions
# And store the formated dates output into another Excel file using the to_excel() function.
demo_file["Dates"] = pd.to_datetime(
    demo_file["Dates"]).dt.strftime("%#d %B, %Y")
demo_file.to_excel("demo_format.xlsx")

Output:

Python Program to Convert any Dates in Spreadsheets 2

Example3

Now let us change the date format as shown below for the same dataset:

Format:

"%B %d, %Y" --> "May 16, 2020"
# Import pandas module as pd using the import keyword 
import pandas as pd
  
# Read the file and indicate which column contains the Dates using the read_excel() function
# and store it in a variable
# (here the file has only 1 column, hence we didn't specify Dates column)
demo_file = pd.read_excel("demo.xlsx")
  
# Convert the Dates column in the above file to "MMMM D, YYY" format using the to_datetime,dt.strftime functions
# And store the formated dates output into another Excel file using the to_excel() function.
demo_file["Dates"] = pd.to_datetime(
    demo_file["Dates"]).dt.strftime("%B %d, %Y")
demo_file.to_excel("demo1_format.xlsx")

Output:

Python Program to Convert any Dates in Spreadsheets 3

Leave a Comment