How to add timestamp to excel file in Python?

If you're working with data in Python and need to track changes or monitor updates, adding a timestamp to your Excel files can be a game-changer. When we are working with large quantities of data, Excel can be used to analyze when specific modifications or events occurred. This can be accomplished by including a timestamp in the Excel file which will tell when a particular modification has been done to a cell.

The modules required for adding timestamps to Excel files are openpyxl and datetime. In this article, we will see how to add timestamps and the modules used to do the same in detail.

DateTime Module

The datetime module can be used to work with both date and time as there are no date or time data types in Python. Python already has the datetime module built in, so we don't need to install it separately.

Date and time manipulation classes are available in the Python datetime module. The main functionality includes working with dates, times, and time periods. Since Date and DateTime are objects in Python rather than strings or timestamps, modifications to them maintain their object properties.

Openpyxl Module

Openpyxl is a Python library for reading and writing Excel files. It is utilized by data scientists for data analysis, data processing, data mining, drawing charts, formatting sheets, and adding formulas, among other tasks.

Key components in openpyxl:

• Workbook ? In openpyxl, a spreadsheet is depicted as a workbook. There are one or more sheets in a workbook.

• Sheet ? A sheet is a single document made up of cells used to organize data.

• Cell ? The intersection of a row and column is referred to as a cell. Typically denoted by A1, B5, etc.

• Row ? A row is a horizontal line indicated by a number (1, 2, etc.).

• Column ? A column is a vertical line denoted by an uppercase letter (A, B, etc.).

Openpyxl can be installed using the pip command, and installation in a virtual environment is recommended. The syntax to install openpyxl using pip is given below:

pip install openpyxl

Adding Timestamp to a Single Cell

Let's start with the basic example of adding a timestamp to a single cell in an Excel file ?

from datetime import datetime
from openpyxl import Workbook

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

# Get the current date and time
now = datetime.now()

# Add the timestamp to cell A1
sheet['A1'] = now.strftime("%Y-%m-%d %H:%M:%S")

# Save the workbook
wb.save('timestamped_file.xlsx')
print("Timestamp added successfully!")

Timestamp added successfully!

Adding Timestamps to Multiple Cells

We can add timestamps to multiple cells with different time intervals. Here's an example that adds timestamps to different rows ?

import time
from datetime import datetime
from openpyxl import Workbook

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

# Add column headers
sheet['A1'] = 'Task'
sheet['B1'] = 'Timestamp'

# Add timestamps to multiple rows
tasks = ['Data Import', 'Data Processing', 'Analysis', 'Report Generation']

for i, task in enumerate(tasks, start=2):
    sheet[f'A{i}'] = task
    sheet[f'B{i}'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    time.sleep(1)  # Wait 1 second between entries

# Save the workbook
wb.save('multiple_timestamps.xlsx')
print("Multiple timestamps added successfully!")

Multiple timestamps added successfully!

Working with Existing Excel Files

You can also add timestamps to existing Excel files by loading them first ?

from datetime import datetime
from openpyxl import Workbook
import os

# First create a sample file to work with
wb = Workbook()
sheet = wb.active
sheet['A1'] = 'Original Data'
wb.save('sample.xlsx')

# Now load the existing file and add timestamp
from openpyxl import load_workbook

# Load the existing workbook
wb = load_workbook('sample.xlsx')
sheet = wb.active

# Add timestamp in the next available cell
sheet['B1'] = 'Last Modified'
sheet['B2'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

# Save changes
wb.save('sample.xlsx')
print("Timestamp added to existing file!")

# Clean up
os.remove('sample.xlsx')

Timestamp added to existing file!

Custom Timestamp Formats

You can customize the timestamp format according to your needs using different strftime() format codes ?

from datetime import datetime
from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

now = datetime.now()

# Different timestamp formats
sheet['A1'] = 'Format'
sheet['B1'] = 'Timestamp'

formats = [
    ('Full DateTime', '%Y-%m-%d %H:%M:%S'),
    ('Date Only', '%Y-%m-%d'),
    ('Time Only', '%H:%M:%S'),
    ('US Format', '%m/%d/%Y %I:%M %p'),
    ('European Format', '%d/%m/%Y %H:%M')
]

for i, (format_name, format_code) in enumerate(formats, start=2):
    sheet[f'A{i}'] = format_name
    sheet[f'B{i}'] = now.strftime(format_code)

wb.save('formatted_timestamps.xlsx')
print("Custom formatted timestamps added!")

# Clean up
import os
os.remove('formatted_timestamps.xlsx')

Custom formatted timestamps added!

Comparison of Methods

Conclusion

Adding timestamps to Excel files in Python using openpyxl and datetime modules provides powerful tracking capabilities for data modifications. Use single timestamps for simple marking, multiple timestamps for sequential tracking, and custom formats for specific display requirements.