Unit 9 of 12
Dates and time
Deadlines, durations, and calendars.
Functions in this unit: DATE, YEAR, MONTH, EOMONTH, DATEDIF, NETWORKDAYS, TEXT, WEEKDAY, EDATE
Key ideas
Dates are numbers
=B2+30Excel counts days, so adding 30 to a date moves it 30 days later.
Month ends and workdays
=EOMONTH(B2,0)EOMONTH finds the last day of a month. NETWORKDAYS counts weekdays between two dates.
Lesson 1: Date basics
How does Excel store a date like Jan 5, 2026?
- As a number counting days
- As text
- As three separate numbers
- As a picture
Show answer
As a number counting days. That is why you can add and subtract dates.
Ship date is the order date plus the days to ship. Write it in D2 so it fills down to D6.
A B C D 1 Order Ordered Days to ship Ships 2 A-100 2026-01-05 3 3 A-101 2026-01-12 7 4 A-102 2026-02-03 2 5 A-103 2026-02-20 10 6 A-104 2026-03-09 5 Hint: =B2+C2
Show answer
=B2+C2in D2. Adding days to a date gives a new date.What will A1 show?
A 1 A1 contains
=DATE(2026,3,15)Show answer
Mar 15, 2026. DATE builds a date from year, month and day.
Pull the year out of each order date. Start in D2 and fill to D6.
A B C D 1 Order Ordered Days to ship Year 2 A-100 2026-01-05 3 3 A-101 2026-01-12 7 4 A-102 2026-02-03 2 5 A-103 2026-02-20 10 6 A-104 2026-03-09 5 Hint: =YEAR(B2)
Show answer
=YEAR(B2)in D2. YEAR returns a plain number you can group or filter by.Build: the month number of the date in B2
Show answer
=MONTH(B2). MONTH returns 1 to 12.
Lesson 2: Date math
How many days from Start to End? Put it in B3.
A B 1 Start 2026-01-05 2 End 2026-03-09 3 Days Hint: Subtract the earlier date from the later one.
Show answer
=B2-B1in B3. Subtracting two dates gives the days between them.Invoices are due at the end of the month they were ordered. Write the due date in D2, fill to D6.
A B C D 1 Order Ordered Days to ship Due 2 A-100 2026-01-05 3 3 A-101 2026-01-12 7 4 A-102 2026-02-03 2 5 A-103 2026-02-20 10 6 A-104 2026-03-09 5 Hint: =EOMONTH(B2,0). The 0 means this month.
Show answer
=EOMONTH(B2,0)in D2. EOMONTH(date, 1) would give the end of next month.What will A1 show?
A 1 A1 contains
=DATEDIF("2025-06-01","2026-03-01","M")Show answer
9. DATEDIF with "M" counts complete months.
What does NETWORKDAYS count?
- Weekdays between two dates, including both ends
- All days between two dates
- Weekends only
- Months between two dates
Show answer
Weekdays between two dates, including both ends. It skips Saturdays and Sundays.
How many workdays are between Start and End? Put it in B3.
A B 1 Start 2026-01-05 2 End 2026-01-30 3 Workdays Hint: =NETWORKDAYS(B1,B2)
Show answer
=NETWORKDAYS(B1,B2)in B3. Perfect for project timelines and SLAs.
Lesson 3: Calendars and labels
Show the full month name of each order in D2. Fill to D6.
A B C D 1 Order Ordered Days to ship Month 2 A-100 2026-01-05 3 3 A-101 2026-01-12 7 4 A-102 2026-02-03 2 5 A-103 2026-02-20 10 6 A-104 2026-03-09 5 Hint: =TEXT(B2,"mmmm")
Show answer
=TEXT(B2,"mmmm")in D2. "mmm" gives Jan, "mmmm" gives January.What will A1 show? Jan 5, 2026 was a Monday.
A 1 A1 contains
=WEEKDAY("2026-01-05")Show answer
2. By default Sunday is 1, so Monday is 2.
Show the weekday name of each order in D2. Fill to D6.
A B C D 1 Order Ordered Days to ship Weekday 2 A-100 2026-01-05 3 3 A-101 2026-01-12 7 4 A-102 2026-02-03 2 5 A-103 2026-02-20 10 6 A-104 2026-03-09 5 Hint: =TEXT(B2,"dddd")
Show answer
=TEXT(B2,"dddd")in D2. "ddd" gives Mon, "dddd" gives Monday.What is the difference between EDATE and EOMONTH?
- EDATE keeps the day, EOMONTH jumps to the month end
- They are the same
- EOMONTH keeps the day
- EDATE counts workdays
Show answer
EDATE keeps the day, EOMONTH jumps to the month end. EDATE(Jan 15, 1) is Feb 15. EOMONTH(Jan 15, 1) is Feb 28.
Subscriptions renew 12 months after the order date. Write the renewal date in D2, fill to D6.
A B C D 1 Order Ordered Days to ship Renews 2 A-100 2026-01-05 3 3 A-101 2026-01-12 7 4 A-102 2026-02-03 2 5 A-103 2026-02-20 10 6 A-104 2026-03-09 5 Hint: =EDATE(B2,12)
Show answer
=EDATE(B2,12)in D2. EDATE handles different month lengths for you.