Excel 时间戳处理指南

本文暂提供英文版，中文翻译正在完善中。

Introduction

Excel stores dates as serial numbers representing the number of days since January 1, 1900 (or January 1, 1904 on Mac). Understanding how Excel handles timestamps is essential for data migration, API integration, and working with date-based analytics. This guide covers conversion techniques, calculations, and best practices.

Understanding Excel Date System

How Excel Stores Dates

Excel uses two date systems:

1900 Date System (Windows - Default)

• Day 1 = January 1, 1900
• Decimal fraction represents time of day (e.g., 0.5 = noon)
• Includes a bug: Excel treats 1900 as a leap year (it wasn't)

1904 Date System (Mac)

• Day 1 = January 2, 1904
• Designed to correct the 1900 leap year bug
• Used by default on older Mac versions of Excel

Important: Always verify which date system your Excel file uses before performing conversions. Mixed date systems in the same workbook can cause significant errors.

Serial Number Structure

Excel Date Number = Days since epoch + Fraction of day

Example: 44927.5
  = 44927 (days since Jan 1, 1900)
  + 0.5 (half day = 12:00 PM)
  = January 1, 2023, 12:00 PM

Basic Conversions

Excel Date to Unix Timestamp

Method 1: Excel Formula

Convert Excel serial date to Unix timestamp (seconds since 1970-01-01):

excel
=(A1 - DATE(1970,1,1)) * 86400

Where:
  A1 = Excel serial date (e.g., 44927.5)
  DATE(1970,1,1) = Unix epoch in Excel (25569)
  86400 = seconds per day

Pro Tip: Use named ranges for better readability:

excel
> =EPOCH_DATE * 86400
>

Where EPOCH_DATE is defined as 25569

Method 2: Excel VBA Function

Create a reusable VBA function for conversions:

vba
Function ExcelToUnix(excelDate As Double) As Long
    ' Convert Excel serial date to Unix timestamp
    Dim epochDate As Date
    epochDate = #1/1/1970#
    ExcelToUnix = (excelDate - epochDate) * 86400
End Function

Method 3: Online Converter

Use our Excel Timestamp Converter tool for instant conversions without formulas.

Unix Timestamp to Excel Date

Method 1: Excel Formula

Convert Unix timestamp to Excel serial date:

excel
=(A1 / 86400) + DATE(1970,1,1)

Where:
  A1 = Unix timestamp in seconds
  86400 = seconds per day
  DATE(1970,1,1) = Unix epoch in Excel (25569)

Method 2: Excel VBA Function

vba
Function UnixToExcel(unixTimestamp As Long) As Date
    ' Convert Unix timestamp to Excel date
    Dim epochDate As Date
    epochDate = #1/1/1970#
    UnixToExcel = (unixTimestamp / 86400) + epochDate
End Function

Date Calculations

Date Arithmetic

Adding and Subtracting Days

excel
' Add 7 days to a date
=A1 + 7

' Subtract 30 days from a date
=A1 - 30

' Calculate days between two dates
=B1 - A1

Adding and Subtracting Time

excel
' Add 4 hours to a datetime
=A1 + (4/24)

' Add 30 minutes to a datetime
=A1 + (30/1440)

' Add 45 seconds to a datetime
=A1 + (45/86400)

' Where:
  24 = hours per day
  1440 = minutes per day
  86400 = seconds per day

Best Practice: Use cell references for time units to avoid hardcoding:

excel
> =A1 + (B1/86400)
>

Where B1 contains seconds to add

Business Days Calculation

excel
' Calculate business days between two dates (excludes weekends)
=NETWORKDAYS(A1, B1)

' Calculate business days excluding holidays
=NETWORKDAYS(A1, B1, C1:C10)

International Version: Use NETWORKDAYS.INTL for custom weekend patterns:

excel
> =NETWORKDAYS.INTL(A1, B1, 11)
>

Where 11 = Sunday and Saturday as weekend

Excel Date Functions

Essential Date Functions

Current Date and Time

excel
' Today's date (no time)
=TODAY()

' Current date and time
=NOW()

' Current time only
=NOW() - TODAY()

' Extract time from datetime
=MOD(A1, 1)

Extracting Date Components

excel
' Extract year
=YEAR(A1)

' Extract month number (1-12)
=MONTH(A1)

' Extract day of month
=DAY(A1)

' Extract hour (0-23)
=HOUR(A1)

' Extract minute (0-59)
=MINUTE(A1)

' Extract second (0-59)
=SECOND(A1)

Creating Dates from Components

excel
' Create date from year, month, day
=DATE(2023, 1, 15)

' Create time from hour, minute, second
=TIME(14, 30, 0)

' Combine date and time
=DATE(2023, 1, 15) + TIME(14, 30, 0)

Working with Weekdays

excel
' Get day of week (1=Sunday, 7=Saturday)
=WEEKDAY(A1)

' Get day of week name
=TEXT(A1, "dddd")

' Get weekday name (3-letter abbreviation)
=TEXT(A1, "ddd")

' ISO week number
=ISOWEEKNUM(A1)

' Week number in year
=WEEKNUM(A1)

Advanced Date Operations

Date Rounding and Truncation

Truncate to Start of Period

excel
' Truncate to start of day (remove time)
=INT(A1)

' Round to nearest hour
=ROUND(A1*24, 0)/24

' Round to nearest day
=ROUND(A1, 0)

' Round down to start of month
=EOMONTH(A1, -1) + 1

' Round down to start of year
=DATE(YEAR(A1), 1, 1)

End of Period Calculations

excel
' End of current month
=EOMONTH(A1, 0)

' End of next month
=EOMONTH(A1, 1)

' End of previous month
=EOMONTH(A1, -1)

' End of current year
=DATE(YEAR(A1), 12, 31)

Conditional Date Logic

Using IF with Dates

excel
' Check if date is in the past
=IF(A1 < TODAY(), "Past", "Future")

' Check if date is within last 30 days
=IF(AND(A1 >= TODAY()-30, A1 <= TODAY()), "Recent", "Old")

' Calculate age
=IF(MONTH(TODAY()) >= MONTH(A1),
    YEAR(TODAY()) - YEAR(A1),
    YEAR(TODAY()) - YEAR(A1) - 1)

Complex Date Logic

excel
' Calculate fiscal year (starts July 1)
=IF(MONTH(A1) >= 7,
    YEAR(A1) + 1,
    YEAR(A1))

' Calculate quarter
=ROUNDUP(MONTH(A1)/3, 0)

' Add business days (skipping weekends)
=WORKDAY(A1, 10)

Date Formatting

Custom Date Formats

Format Codes

Format Code Examples:

d          Day (1-31)
dd         Day with leading zero (01-31)
ddd        Day abbreviation (Mon)
dddd       Full day name (Monday)

m          Month (1-12)
mm         Month with leading zero (01-12)
mmm        Month abbreviation (Jan)
mmmm       Full month name (January)

yy         2-digit year (23)
yyyy       4-digit year (2023)

h          Hour (0-23)
hh         Hour with leading zero (00-23)
m          Minute (0-59)
mm         Minute with leading zero (00-59)
s          Second (0-59)
ss         Second with leading zero (00-59)

AM/PM      AM/PM indicator

Applying Formats

excel
' Apply date format via formula
=TEXT(A1, "yyyy-mm-dd")

' Apply datetime format with seconds
=TEXT(A1, "yyyy-mm-dd hh:mm:ss")

' Custom format: "January 15, 2023 at 2:30 PM"
=TEXT(A1, "mmmm dd, yyyy at h:mm AM/PM")

' ISO 8601 format
=TEXT(A1, "yyyy-mm-ddThh:mm:ss")

Important: TEXT() returns a string value. Use numeric operations first, then format at the end.

Common Pitfalls

Date System Conflicts

1900 vs 1904 Date System

Problem: Mixed date systems cause significant calculation errors.

excel
' Check if workbook uses 1904 date system
' Excel Options > Advanced > When calculating this workbook > Use 1904 date system

Solution: Always use Tools > Options > Advanced to verify date system settings before working with dates.

Leap Year Bug (1900)

Excel incorrectly treats 1900 as a leap year, including February 29, 1900 (which didn't exist):

excel
' March 1, 1900 in Excel
=DATE(1900, 3, 1)  ' Returns 61 (correct)

' February 28, 1900 in Excel
=DATE(1900, 2, 28)  ' Returns 59 (correct)

' February 29, 1900 (DOESN'T EXIST)
=DATE(1900, 2, 29)  ' Returns 60 (incorrect - this day never happened)

Workaround: The 1904 date system corrects this bug but introduces compatibility issues with Windows Excel files.

Time Zone Issues

Excel stores dates without timezone information:

excel
' Current time in Excel
=NOW()  ' Returns local system time

Best Practice: Store UTC timestamps in separate column and maintain timezone metadata for conversions.

Text vs Date Formats

Problem: Dates stored as text can't be used in calculations.

excel
' Check if cell contains date or text
=ISNUMBER(A1)  ' TRUE for dates, FALSE for text

' Convert text to date
=DATEVALUE("2023-01-15")
=TIMEVALUE("14:30:00")

' Parse combined date and time text
=DATEVALUE("2023-01-15") + TIMEVALUE("14:30:00")

Performance Optimization

Calculation Performance

Avoid Volatile Functions

Problem: Functions like NOW() and TODAY() recalculate on every change, slowing down large workbooks.

Solution: Store volatile function results in static cells:

excel
> ' BAD: Every row recalculates
> =IF(A1 > NOW(), "Future", "Past")
>
> ' GOOD: Calculate once in cell B1
> B1: =NOW()
> A2: =IF(A1 > $B$1, "Future", "Past")
>

Use Named Ranges

excel
> ' Define named ranges (Formulas > Name Manager)
>   EPOCH_DATE = 25569
>   SECONDS_PER_DAY = 86400
>
> ' Use in formulas
> =(A1 - EPOCH_DATE) * SECONDS_PER_DAY
>

Benefit: Named ranges improve readability and make formulas easier to maintain.

Array Formulas for Batch Operations

excel
' Convert multiple Unix timestamps at once (Excel 365)
=A2:A100/86400 + DATE(1970,1,1)

' Calculate ages for a range
=YEAR(TODAY()) - YEAR(A2:A100)

Integration Examples

Exporting to CSV

When exporting Excel dates to CSV:

excel
' Format dates as ISO 8601 before exporting
=TEXT(A1, "yyyy-mm-ddThh:mm:ss")

Best Practice: Save as CSV with UTF-8 encoding to preserve date formats.

Importing from API

Parse JSON dates from APIs:

excel
' Assuming cell A1 contains: 1673761800 (Unix timestamp)
' Convert to Excel date
=(A1/86400) + DATE(1970,1,1)

' Format as readable date
=TEXT((A1/86400) + DATE(1970,1,1), "yyyy-mm-dd hh:mm:ss")

Database Integration

Working with SQL databases:

excel
' Prepare date for SQL INSERT
=TEXT(A1, "yyyy-mm-dd hh:mm:ss")
' Returns: "2023-01-15 14:30:00"

' Convert SQL DATETIME to Excel
=DATEVALUE(LEFT(A1, 10)) + TIMEVALUE(MID(A1, 12, 8))

Best Practices Summary

Date Handling Checklist

✅ Always verify date system (1900 vs 1904) before calculations
✅ Use named ranges for constants (EPOCH_DATE, SECONDS_PER_DAY)
✅ Store volatile function results (NOW(), TODAY()) in static cells
✅ Use TEXT() only for final display formatting
✅ Validate date formats before calculations (ISNUMBER, DATEVALUE)
✅ Use NETWORKDAYS for business day calculations
✅ Apply consistent date formatting across workbooks
✅ Document date assumptions (timezones, epoch references)
✅ Test edge cases (leap years, month boundaries)
❌ Don't mix text and date formats in calculations
❌ Don't use volatile functions in large datasets
❌ Don't assume dates are in UTC without documentation
❌ Don't hardcode timezone offsets in formulas
❌ Don't ignore the 1900 leap year bug for historical dates

Troubleshooting Common Issues

Issue: Dates Display as Numbers

excel
' Solution 1: Format as date
' Select cells > Right-click > Format Cells > Date

' Solution 2: Use TEXT() function
=TEXT(A1, "yyyy-mm-dd")

' Solution 3: Check for text dates
' If ISNUMBER(A1) returns FALSE, use:
=DATEVALUE(A1)

Issue: Incorrect Date Calculations

excel
' Check 1: Verify date system
' Excel Options > Advanced > Use 1904 date system

' Check 2: Ensure consistent units
' Days: =A1 + 7
' Hours: =A1 + (4/24)
' Minutes: =A1 + (30/1440)
' Seconds: =A1 + (45/86400)

' Check 3: Verify date components
' Ensure DATE() has correct arguments: YEAR, MONTH, DAY

Issue: Time Zone Conversion Errors

excel
' Solution: Maintain separate timezone column
A1: Excel timestamp
B1: Timezone offset (e.g., -5 for EST)
C1: =A1 + (B1/24)

Related Tools

• Excel Timestamp Converter - Instant conversion between Excel serial dates and Unix timestamps
• Timestamp Format Converter - Convert between multiple timestamp formats
• Date Calculator - Add or subtract days, weeks, months from dates
• Current Timestamp - Get current timestamp in multiple formats
• Unix Timestamp Converter - Convert Unix timestamps to dates

Additional Resources

• Microsoft Excel Date Functions Documentation
• Excel Date System Best Practices
• Understanding Excel Date Calculations

For large-scale date processing (>10,000 rows), consider using Power Query or VBA macros for better performance. Excel functions are optimized for interactive use and can become slow with massive datasets.