本文暂提供英文版，中文翻译正在完善中。
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
TEXT1Excel Date Number = Days since epoch + Fraction of day 2 3Example: 44927.5 4 = 44927 (days since Jan 1, 1900) 5 + 0.5 (half day = 12:00 PM) 6 = 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):
EXCEL1=(A1 - DATE(1970,1,1)) * 86400 2 3Where: 4 A1 = Excel serial date (e.g., 44927.5) 5 DATE(1970,1,1) = Unix epoch in Excel (25569) 6 86400 = seconds per day
Pro Tip: Use named ranges for better readability:
EXCEL1=EPOCH_DATE * 86400Where EPOCH_DATE is defined as 25569
Method 2: Excel VBA Function
Create a reusable VBA function for conversions:
VBA1Function ExcelToUnix(excelDate As Double) As Long 2 ' Convert Excel serial date to Unix timestamp 3 Dim epochDate As Date 4 epochDate = #1/1/1970# 5 ExcelToUnix = (excelDate - epochDate) * 86400 6End 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:
EXCEL1=(A1 / 86400) + DATE(1970,1,1) 2 3Where: 4 A1 = Unix timestamp in seconds 5 86400 = seconds per day 6 DATE(1970,1,1) = Unix epoch in Excel (25569)
Method 2: Excel VBA Function
VBA1Function UnixToExcel(unixTimestamp As Long) As Date 2 ' Convert Unix timestamp to Excel date 3 Dim epochDate As Date 4 epochDate = #1/1/1970# 5 UnixToExcel = (unixTimestamp / 86400) + epochDate 6End Function
Date Calculations
Date Arithmetic
Adding and Subtracting Days
EXCEL1' Add 7 days to a date 2=A1 + 7 3 4' Subtract 30 days from a date 5=A1 - 30 6 7' Calculate days between two dates 8=B1 - A1
Adding and Subtracting Time
EXCEL1' Add 4 hours to a datetime 2=A1 + (4/24) 3 4' Add 30 minutes to a datetime 5=A1 + (30/1440) 6 7' Add 45 seconds to a datetime 8=A1 + (45/86400) 9 10' Where: 11 24 = hours per day 12 1440 = minutes per day 13 86400 = seconds per day
Best Practice: Use cell references for time units to avoid hardcoding:
EXCEL1=A1 + (B1/86400)Where B1 contains seconds to add
Business Days Calculation
EXCEL1' Calculate business days between two dates (excludes weekends) 2=NETWORKDAYS(A1, B1) 3 4' Calculate business days excluding holidays 5=NETWORKDAYS(A1, B1, C1:C10)
International Version: Use NETWORKDAYS.INTL for custom weekend patterns:
EXCEL1=NETWORKDAYS.INTL(A1, B1, 11)Where 11 = Sunday and Saturday as weekend
Excel Date Functions
Essential Date Functions
Current Date and Time
EXCEL1' Today's date (no time) 2=TODAY() 3 4' Current date and time 5=NOW() 6 7' Current time only 8=NOW() - TODAY() 9 10' Extract time from datetime 11=MOD(A1, 1)
Extracting Date Components
EXCEL1' Extract year 2=YEAR(A1) 3 4' Extract month number (1-12) 5=MONTH(A1) 6 7' Extract day of month 8=DAY(A1) 9 10' Extract hour (0-23) 11=HOUR(A1) 12 13' Extract minute (0-59) 14=MINUTE(A1) 15 16' Extract second (0-59) 17=SECOND(A1)
Creating Dates from Components
EXCEL1' Create date from year, month, day 2=DATE(2023, 1, 15) 3 4' Create time from hour, minute, second 5=TIME(14, 30, 0) 6 7' Combine date and time 8=DATE(2023, 1, 15) + TIME(14, 30, 0)
Working with Weekdays
EXCEL1' Get day of week (1=Sunday, 7=Saturday) 2=WEEKDAY(A1) 3 4' Get day of week name 5=TEXT(A1, "dddd") 6 7' Get weekday name (3-letter abbreviation) 8=TEXT(A1, "ddd") 9 10' ISO week number 11=ISOWEEKNUM(A1) 12 13' Week number in year 14=WEEKNUM(A1)
Advanced Date Operations
Date Rounding and Truncation
Truncate to Start of Period
EXCEL1' Truncate to start of day (remove time) 2=INT(A1) 3 4' Round to nearest hour 5=ROUND(A1*24, 0)/24 6 7' Round to nearest day 8=ROUND(A1, 0) 9 10' Round down to start of month 11=EOMONTH(A1, -1) + 1 12 13' Round down to start of year 14=DATE(YEAR(A1), 1, 1)
End of Period Calculations
EXCEL1' End of current month 2=EOMONTH(A1, 0) 3 4' End of next month 5=EOMONTH(A1, 1) 6 7' End of previous month 8=EOMONTH(A1, -1) 9 10' End of current year 11=DATE(YEAR(A1), 12, 31)
Conditional Date Logic
Using IF with Dates
EXCEL1' Check if date is in the past 2=IF(A1 < TODAY(), "Past", "Future") 3 4' Check if date is within last 30 days 5=IF(AND(A1 >= TODAY()-30, A1 <= TODAY()), "Recent", "Old") 6 7' Calculate age 8=IF(MONTH(TODAY()) >= MONTH(A1), 9 YEAR(TODAY()) - YEAR(A1), 10 YEAR(TODAY()) - YEAR(A1) - 1)
Complex Date Logic
EXCEL1' Calculate fiscal year (starts July 1) 2=IF(MONTH(A1) >= 7, 3 YEAR(A1) + 1, 4 YEAR(A1)) 5 6' Calculate quarter 7=ROUNDUP(MONTH(A1)/3, 0) 8 9' Add business days (skipping weekends) 10=WORKDAY(A1, 10)
Date Formatting
Custom Date Formats
Format Codes
TEXT1Format Code Examples: 2 3d Day (1-31) 4dd Day with leading zero (01-31) 5ddd Day abbreviation (Mon) 6dddd Full day name (Monday) 7 8m Month (1-12) 9mm Month with leading zero (01-12) 10mmm Month abbreviation (Jan) 11mmmm Full month name (January) 12 13yy 2-digit year (23) 14yyyy 4-digit year (2023) 15 16h Hour (0-23) 17hh Hour with leading zero (00-23) 18m Minute (0-59) 19mm Minute with leading zero (00-59) 20s Second (0-59) 21ss Second with leading zero (00-59) 22 23AM/PM AM/PM indicator
Applying Formats
EXCEL1' Apply date format via formula 2=TEXT(A1, "yyyy-mm-dd") 3 4' Apply datetime format with seconds 5=TEXT(A1, "yyyy-mm-dd hh:mm:ss") 6 7' Custom format: "January 15, 2023 at 2:30 PM" 8=TEXT(A1, "mmmm dd, yyyy at h:mm AM/PM") 9 10' ISO 8601 format 11=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.
EXCEL1' Check if workbook uses 1904 date system 2' 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):
EXCEL1' March 1, 1900 in Excel 2=DATE(1900, 3, 1) ' Returns 61 (correct) 3 4' February 28, 1900 in Excel 5=DATE(1900, 2, 28) ' Returns 59 (correct) 6 7' February 29, 1900 (DOESN'T EXIST) 8=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:
EXCEL1' Current time in Excel 2=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.
EXCEL1' Check if cell contains date or text 2=ISNUMBER(A1) ' TRUE for dates, FALSE for text 3 4' Convert text to date 5=DATEVALUE("2023-01-15") 6=TIMEVALUE("14:30:00") 7 8' Parse combined date and time text 9=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:
EXCEL1' BAD: Every row recalculates 2=IF(A1 > NOW(), "Future", "Past") 3 4' GOOD: Calculate once in cell B1 5B1: =NOW() 6A2: =IF(A1 > $B$1, "Future", "Past")Use Named Ranges
EXCEL1' Define named ranges (Formulas > Name Manager) 2 EPOCH_DATE = 25569 3 SECONDS_PER_DAY = 86400 4 5' Use in formulas 6=(A1 - EPOCH_DATE) * SECONDS_PER_DAYBenefit: Named ranges improve readability and make formulas easier to maintain.
Array Formulas for Batch Operations
EXCEL1' Convert multiple Unix timestamps at once (Excel 365) 2=A2:A100/86400 + DATE(1970,1,1) 3 4' Calculate ages for a range 5=YEAR(TODAY()) - YEAR(A2:A100)
Integration Examples
Exporting to CSV
When exporting Excel dates to CSV:
EXCEL1' Format dates as ISO 8601 before exporting 2=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:
EXCEL1' Assuming cell A1 contains: 1673761800 (Unix timestamp) 2' Convert to Excel date 3=(A1/86400) + DATE(1970,1,1) 4 5' Format as readable date 6=TEXT((A1/86400) + DATE(1970,1,1), "yyyy-mm-dd hh:mm:ss")
Database Integration
Working with SQL databases:
EXCEL1' Prepare date for SQL INSERT 2=TEXT(A1, "yyyy-mm-dd hh:mm:ss") 3' Returns: "2023-01-15 14:30:00" 4 5' Convert SQL DATETIME to Excel 6=DATEVALUE(LEFT(A1, 10)) + TIMEVALUE(MID(A1, 12, 8))
Best Practices Summary
Date Handling Checklist
TEXT1✅ Always verify date system (1900 vs 1904) before calculations 2✅ Use named ranges for constants (EPOCH_DATE, SECONDS_PER_DAY) 3✅ Store volatile function results (NOW(), TODAY()) in static cells 4✅ Use TEXT() only for final display formatting 5✅ Validate date formats before calculations (ISNUMBER, DATEVALUE) 6✅ Use NETWORKDAYS for business day calculations 7✅ Apply consistent date formatting across workbooks 8✅ Document date assumptions (timezones, epoch references) 9✅ Test edge cases (leap years, month boundaries) 10❌ Don't mix text and date formats in calculations 11❌ Don't use volatile functions in large datasets 12❌ Don't assume dates are in UTC without documentation 13❌ Don't hardcode timezone offsets in formulas 14❌ Don't ignore the 1900 leap year bug for historical dates
Troubleshooting Common Issues
Issue: Dates Display as Numbers
EXCEL1' Solution 1: Format as date 2' Select cells > Right-click > Format Cells > Date 3 4' Solution 2: Use TEXT() function 5=TEXT(A1, "yyyy-mm-dd") 6 7' Solution 3: Check for text dates 8' If ISNUMBER(A1) returns FALSE, use: 9=DATEVALUE(A1)
Issue: Incorrect Date Calculations
EXCEL1' Check 1: Verify date system 2' Excel Options > Advanced > Use 1904 date system 3 4' Check 2: Ensure consistent units 5' Days: =A1 + 7 6' Hours: =A1 + (4/24) 7' Minutes: =A1 + (30/1440) 8' Seconds: =A1 + (45/86400) 9 10' Check 3: Verify date components 11' Ensure DATE() has correct arguments: YEAR, MONTH, DAY
Issue: Time Zone Conversion Errors
EXCEL1' Solution: Maintain separate timezone column 2A1: Excel timestamp 3B1: Timezone offset (e.g., -5 for EST) 4C1: =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.