Dates and times are essential in almost every spreadsheet—from project schedules to invoice due dates to employee attendance. But if you’re new to Google Sheets, you’ve probably run into strange formatting, confusing calculations, or incorrect results when working with dates and times. Don’t worry—this guide will help you understand how to use, format, and calculate with dates and times effortlessly.
In this article, you’ll learn how Google Sheets handles date and time values, how to format them correctly, and how to use popular functions to automate date-based calculations. It’s easier than you think, and once you’ve mastered these tools, you’ll save time and avoid common mistakes.
Understanding How Dates and Times Work in Google Sheets
Google Sheets stores dates and times as numbers behind the scenes. For example:
- Jan 1, 2024 is stored as
45291 - 12:00 PM is stored as
0.5 - Jan 1, 2024 12:00 PM becomes
45291.5
Why does this matter? Because when you’re using formulas or applying formatting, understanding this structure helps avoid errors like seeing “########” instead of a date.
How to Format Dates and Times in Google Sheets
To change how your dates or times appear:
- Select the cells you want to format
- Go to Format > Number
- Choose Date, Time, or Custom date and time
Here are some popular date formatting options:
| Format | Output Example |
|---|---|
| MM/DD/YYYY | 04/07/2025 |
| DD-MMM-YYYY | 07-Apr-2025 |
| MMMM D, YYYY | April 7, 2025 |
| HH:MM AM/PM | 02:30 PM |
| Custom: DDDD | Monday |
Common Date and Time Functions
1. TODAY()
Returns the current date (updates automatically each day).
=TODAY()
2. NOW()
Returns the current date and time.
=NOW()
3. DATEDIF(start_date, end_date, unit)
Calculates the difference between two dates.
=DATEDIF(A2, B2, "D")
This gives the number of days between two dates.
4. TEXT(date, format)
Formats a date into a custom text string.
=TEXT(A2, "dddd, mmmm d, yyyy")
Outputs something like: Monday, April 7, 2025
5. WORKDAY(start_date, num_days, [holidays])
Returns a future or past workday, excluding weekends and optional holidays.
=WORKDAY(A2, 5)
Returns the date 5 workdays after A2.
Real-Life Example: Tracking Project Deadlines
Imagine you manage a small team and want to track project tasks with their assigned and due dates, factoring in business days.
| Task | Assigned Date | Days to Complete | Due Date |
|---|---|---|---|
| Design Mockup | 04/01/2025 | 5 | =WORKDAY(B2, C2) |
| Client Feedback | 04/03/2025 | 3 | =WORKDAY(B3, C3) |
This way, weekends are automatically skipped, and you get accurate due dates for work planning.
Time Calculations Made Easy
How to Calculate Time Differences
If you want to calculate how many hours passed between two times:
=TEXT(B2 - A2, "h:mm")
Or if you’re billing based on hours worked:
=(B2 - A2) * 24
This gives you the total hours in decimal format (e.g., 7.5 hours).
Quick Tips for Avoiding Date/Time Errors
- Always check your date format settings (File > Settings > Locale).
- Use
DATE(year, month, day)to construct dates safely in formulas. - Watch out for text-formatted dates—they can break calculations.
- Use
ISDATE()(custom formula) orIFERROR()to handle invalid inputs.
Quick Cheat Sheet: Dates & Times in Google Sheets
- Current Date:
=TODAY() - Current Time:
=NOW() - Date Difference:
=DATEDIF(A2, B2, "D") - Add Days:
=A2 + 7(adds 7 days) - Next Workday:
=WORKDAY(A2, 3) - Format as Text:
=TEXT(A2, "MMMM D, YYYY") - Time Difference (hours):
=(B2 - A2) * 24
Working with dates and times in Google Sheets might seem tricky at first, but once you understand how they’re stored and how to format or calculate them, it becomes second nature. Whether you’re scheduling tasks, logging hours, or tracking deadlines, these tools will help you stay accurate and organized.
Start experimenting with the examples above, and soon you’ll be using Google Sheets like a pro—no more manual math or formatting headaches!