As an employee or an HR professional, tracking the number of days worked or calculating the duration between specific dates, such as start and end dates or promotion dates, is crucial for various purposes, including payroll calculations, leave management, and performance evaluations. Excel is a powerful tool for managing such data, but manually calculating these durations can be time-consuming and prone to errors.

Python automation provides a solution to streamline these calculations by leveraging the openpyxl library to manipulate Excel files. This tutorial will demonstrate how to use Python to automate the process of finding the number of days between two dates in Excel, enabling you to efficiently manage and analyze time-related data.

Let’s get started !

First of all let’s see how can you calculate the number of days between a start date and an end date and takes some data, writes it to an Excel sheet, and saves the workbook.

from datetime import datetime
from openpyxl import Workbook
from openpyxl.utils import get_column_letter

# Function to calculate number of days between two dates
def calculate_days(start_date, end_date):
    days = (end_date - start_date).days
    return days

# Create a new Excel workbook
wb = Workbook()
ws = wb.active

# Input start date and end date
start_date = datetime.strptime(input("Enter start date (YYYY-MM-DD): "), "%Y-%m-%d")
end_date = datetime.strptime(input("Enter end date (YYYY-MM-DD): "), "%Y-%m-%d")

# Calculate number of days between start date and end date
days = calculate_days(start_date, end_date)

# Write data to Excel sheet
ws['A1'] = 'Start Date'
ws['B1'] = 'End Date'
ws['C1'] = 'Number of Days'
ws.append([start_date, end_date, days])

# Save the workbook
wb.save('output.xlsx')
print("Data saved to output.xlsx")

Explanation of the above code:

1. Import necessary modules:
   • datetime from the datetime module to work with dates.
   • Workbook from openpyxl to create a new Excel workbook.
   • get_column_letter from openpyxl.utils to convert column numbers to letters.
2. Define a function calculate_days:
   • This function takes two arguments, start_date and end_date, representing the start and end dates.
   • It calculates the number of days between the two dates using the days attribute of the timedelta object obtained by subtracting start_date from end_date.
   • Returns the number of days as an integer.
3. Create a new Excel workbook:
   • Initialize a new Workbook object and assign it to the variable wb.
   • Get the active sheet of the workbook and assign it to the variable ws.
4. Input start date and end date:
   • Use datetime.strptime to convert user input (in the format YYYY-MM-DD) to a datetime object for both start and end dates.
5. Calculate the number of days:
   • Call the calculate_days function with start_date and end_date as arguments to get the number of days between them.
6. Write data to Excel sheet:
   • Set the column headers in the first row of the worksheet (ws) for ‘Start Date’, ‘End Date’, and ‘Number of Days’.
   • Append a row containing the start date, end date, and number of days to the worksheet.
7. Save the workbook:
   • Save the workbook to a file named ‘output.xlsx’ using the save method of the wb object.
8. Print a message:
   • Print a message indicating that the data has been saved to the Excel file.

Output:

Now lets say we want to To calculate the difference between two dates in years and months, then you can use the dateutil library to handle date arithmetic more effectively. Here’s the modified code:

from datetime import datetime
from dateutil.relativedelta import relativedelta
from openpyxl import Workbook

# Function to calculate number of years and months between two dates
def calculate_years_months(start_date, end_date):
    delta = relativedelta(end_date, start_date)
    years = delta.years
    months = delta.months
    return years, months

# Create a new Excel workbook
wb = Workbook()
ws = wb.active

# Input start date and end date
start_date = datetime.strptime(input("Enter start date (YYYY-MM-DD): "), "%Y-%m-%d")
end_date = datetime.strptime(input("Enter end date (YYYY-MM-DD): "), "%Y-%m-%d")

# Calculate number of years and months between start date and end date
years, months = calculate_years_months(start_date, end_date)

# Write data to Excel sheet
ws['A1'] = 'Start Date'
ws['B1'] = 'End Date'
ws['C1'] = 'Years'
ws['D1'] = 'Months'
ws.append([start_date, end_date, years, months])

# Save the workbook
wb.save('output.xlsx')
print("Data saved to output.xlsx")

Output:

Wooh ! Hope you have achived this now let me take you to next step

where we want to include employee names, total experience, and handle multiple company start and end dates, you can modify the code as follows. This code uses dictionaries to store employee information, including multiple company details, and calculates the total experience in years and months:

from datetime import datetime
from dateutil.relativedelta import relativedelta
from openpyxl import Workbook

# Function to calculate number of years and months between two dates
def calculate_years_months(start_date, end_date):
    delta = relativedelta(end_date, start_date)
    years = delta.years
    months = delta.months
    return years, months

# Create a new Excel workbook
wb = Workbook()
ws = wb.active

# Input employee details
employee_data = {
    'name': input("Enter employee name: "),
    'experience': {}
}

# Input company details
while True:
    company_name = input("Enter company name (or 'done' to finish): ")
    if company_name.lower() == 'done':
        break
    else:
        start_date = datetime.strptime(input(f"Enter start date for {company_name} (YYYY-MM-DD): "), "%Y-%m-%d")
        end_date = datetime.strptime(input(f"Enter end date for {company_name} (YYYY-MM-DD): "), "%Y-%m-%d")
        years, months = calculate_years_months(start_date, end_date)
        employee_data['experience'][company_name] = {'start_date': start_date, 'end_date': end_date, 'years': years, 'months': months}

# Calculate total experience
total_years = sum(exp['years'] for exp in employee_data['experience'].values())
total_months = sum(exp['months'] for exp in employee_data['experience'].values())
total_years += total_months // 12
total_months = total_months % 12

# Write data to Excel sheet
ws['A1'] = 'Employee Name'
ws['B1'] = 'Company Name'
ws['C1'] = 'Start Date'
ws['D1'] = 'End Date'
ws['E1'] = 'Years'
ws['F1'] = 'Months'

row = 2
for company, exp_data in employee_data['experience'].items():
    ws[f'A{row}'] = employee_data['name']
    ws[f'B{row}'] = company
    ws[f'C{row}'] = exp_data['start_date']
    ws[f'D{row}'] = exp_data['end_date']
    ws[f'E{row}'] = exp_data['years']
    ws[f'F{row}'] = exp_data['months']
    row += 1

# Write total experience
ws[f'A{row}'] = employee_data['name']
ws[f'B{row}'] = 'Total Experience'
ws[f'E{row}'] = total_years
ws[f'F{row}'] = total_months

# Save the workbook
wb.save('output.xlsx')
print("Data saved to output.xlsx")

Output:

Explanation of the above code:

This Python code allows you to input employee details, including multiple companies with start and end dates, and calculates the total experience in years and months. The data is then written to an Excel file. Here’s a detailed explanation:

1. Import necessary modules:
   • datetime from the datetime module to work with dates.
   • relativedelta from dateutil.relativedelta to calculate the difference between two dates in years and months.
   • Workbook from openpyxl to create a new Excel workbook.
2. Define a function calculate_years_months:
   • This function takes two arguments, start_date and end_date, representing the start and end dates.
   • It calculates the difference between the two dates using relativedelta and extracts the number of years and months.
   • Returns the number of years and months as a tuple.
3. Create a new Excel workbook:
   • Initialize a new Workbook object and assign it to the variable wb.
   • Get the active sheet of the workbook and assign it to the variable ws.
4. Input employee details:
   • Prompt the user to enter the employee’s name.
5. Input company details:
   • Use a while loop to repeatedly prompt the user to enter company details (name, start date, end date) until ‘done’ is entered.
   • Convert start and end dates to datetime objects and calculate the years and months of experience using the calculate_years_months function.
   • Store the company details in the employee_data dictionary.
6. Calculate total experience:
   • Sum the years and months of experience for each company.
   • Convert any additional months to years and update the total years of experience.
7. Write data to Excel sheet:
   • Set the column headers in the first row of the worksheet (ws) for ‘Employee Name’, ‘Company Name’, ‘Start Date’, ‘End Date’, ‘Years’, and ‘Months’.
   • Append rows containing the employee’s name, company name, start date, end date, years, and months of experience for each company.
   • Update the row counter (row) for each iteration.
8. Write total experience:
   • Append a row containing the employee’s name and the total years and months of experience.
9. Save the workbook:
   • Save the workbook to a file named ‘output.xlsx’ using the save method of the wb object.
10. Print a message:
    • Print a message indicating that the data has been saved to the Excel file.

This code provides a comprehensive example of how to calculate and write employee experience data to an Excel file using Python.