Working with Date Time

---

🕒 Working with DateTime in CSV and Excel Files — A Cross-Platform Guide for Engineers and Data Analysts

Whether you're an engineer, analyst, or researcher, handling date and time data across CSV and Excel files often leads to formatting headaches — especially when using both Linux and Windows systems. Things get even more complicated when working with microsecond precision, and Microsoft Excel can wreak havoc by reformatting your data without warning.

This article offers practical solutions to handle datetime data cleanly, consistently, and safely — across platforms.

---

✅ What’s the Best Format for Storing DateTime?

Always use the ISO 8601 extended format with microseconds:

YYYY-MM-DD HH:MM:SS.ffffff

🔹 Example: 2025-07-04 14:23:10.123456

Why this format?

• ✅ Unambiguous (no MM/DD vs DD/MM confusion)
• ✅ Alphabetically sortable
• ✅ Works with pandas, openpyxl, matplotlib, and most databases
• ✅ Compatible with both Linux and Windows
• ✅ Preserves microsecond-level precision

---

⚠️ The Excel Problem

Opening a CSV with datetimes in Excel can lead to:

• ❌ Misinterpreted dates (e.g., 04/07/2025 → 07-Apr-2025)
• ❌ Lost microsecond precision
• ❌ Unexpected formatting (AM/PM vs 24-hour)

---

🛡️ How to Prevent Excel from Changing Your DateTime Format

1. Wrap the datetime in double quotes when writing CSV

df['DateTime'] = df['DateTime'].apply(lambda x: f'"{x}"')
df.to_csv('output.csv', index=False)

2. Prefix the string with an apostrophe

Excel treats this as a literal string:

df['DateTime'] = df['DateTime'].apply(lambda x: f"'{x}")

3. Use .xlsx format and control the format in Python

from openpyxl import Workbook
from openpyxl.styles import numbers

wb = Workbook()
ws = wb.active
ws['A1'] = 'DateTime'
cell = ws['A2']
cell.value = '2025-07-04 14:23:10.123456'
cell.number_format = 'yyyy-mm-dd hh:mm:ss.000000'
wb.save('example.xlsx')

Note: Excel may not display microseconds in the UI but retains the value in the cell.

4. Keep datetime as a string while exporting CSV

df['DateTime'] = pd.to_datetime(df['DateTime']).dt.strftime('%Y-%m-%d %H:%M:%S.%f')
df.to_csv('data.csv', index=False)

---

📈 Can You Still Plot with Pandas if DateTime is Stored as Text?

Yes! Here's how:

✅ Convert to datetime before plotting

import pandas as pd
import matplotlib.pyplot as plt

# Load CSV
df = pd.read_csv('data.csv')

# Convert string to datetime
df['DateTime'] = pd.to_datetime(df['DateTime'])

# Optional: Set as index
df.set_index('DateTime', inplace=True)

# Plot
df['Temperature'].plot()
plt.xlabel('Date and Time')
plt.ylabel('Temperature (°C)')
plt.title('Temperature vs Time')
plt.grid(True)
plt.tight_layout()
plt.show()

🕵️ Show microseconds in x-axis (if needed)

import matplotlib.dates as mdates
plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%H:%M:%S.%f'))

---

🔁 Summary Table

Goal	Recommended Practice
Clear format	YYYY-MM-DD HH:MM:SS.ffffff
Prevent Excel reformatting	Quote text or use .xlsx format with manual formatting
Preserve microseconds	Store as string using strftime('%Y-%m-%d %H:%M:%S.%f')
Plot from text datetime	Convert using pd.to_datetime() before plotting
Excel compatibility	Wrap with " or ' or use openpyxl

---

🧰 Need a Reusable Tool?

If you'd like a Python template to:

• Load CSVs with datetime columns
• Preserve format for Excel
• Plot any Y-variable against time

Let me know — I’ll be happy to share a ready-to-use function for your workflow.

---