Purpose
By the end of this lesson, you will be able to perform common date and time calculations used in business reporting.
Lesson Explanation
Excel stores dates as serial numbers internally (January 1, 1900 is day 1), which is why dates can be subtracted directly: =C2-B2 with two dates returns the number of days between them.
TODAY() returns the current date, updating automatically each day the file is opened – useful for calculating age, days overdue, or time elapsed: =TODAY()-B2 returns the number of days since the date in B2.
DATEDIF calculates the difference between two dates in years, months, or days: =DATEDIF(B2,C2,”Y”) returns the number of complete years between two dates – useful for calculating tenure or age precisely.
EOMONTH returns the last day of a month, a specified number of months before or after a given date: =EOMONTH(B2,0) returns the last day of the month containing the date in B2; =EOMONTH(B2,1) returns the last day of the following month – useful for generating billing or reporting periods.
NETWORKDAYS counts business days (excluding weekends, and optionally a list of holidays) between two dates: =NETWORKDAYS(B2,C2) returns the number of weekdays between the two dates.
Practice Questions
1. A project started on the date in cell B2 and ended on the date in C2. Write a formula that calculates the number of days the project took.
View Answer
=C2-B22. Write a formula that calculates how many days have passed since the date in cell B2, using today’s date.
View Answer
=TODAY()-B23. An HR system needs to calculate an employee’s exact tenure in complete years, using their hire date in B2 and today’s date. Write this formula.
View Answer
=DATEDIF(B2,TODAY(),”Y”)4. A billing report needs the last day of the month for a date in cell B2 (the same month, not a future or past one). Write this formula.
View Answer
=EOMONTH(B2,0)5. A report needs to find the last day of the month that comes 3 months AFTER the date in B2. Write this formula.
View Answer
=EOMONTH(B2,3)6. A project manager needs to know how many working days (excluding weekends) fall between a start date in B2 and an end date in C2, with no holidays to consider. Write this formula.
View Answer
=NETWORKDAYS(B2,C2)7. A formula =TODAY()-B2 returns a negative number for a specific row. What does this most likely indicate?
View Answer
The date in B2 is in the future relative to today, meaning that row’s event hasn’t happened yet – a useful check for catching data entry errors or upcoming deadlines.
8. Why does this lesson explain that Excel stores dates as serial numbers, rather than treating them as pure text?
View Answer
This is what allows arithmetic operations, like subtracting one date from another to get a day count, to work directly – a text-based date could not be calculated with this way.
9. An invoice needs to be considered “overdue” if more than 30 days have passed since its due date in cell B2. Write an IF formula that returns “Overdue” or “Current” based on this rule, using today’s date.
View Answer
=IF(TODAY()-B2>30,”Overdue”,”Current”)10. A report needs to count business days between an order date (B2) and a ship date (C2), while also excluding a specific list of company holidays stored in the range E2:E10. What additional argument would NETWORKDAYS need for this?
View Answer
An optional third argument listing the holiday range: =NETWORKDAYS(B2,C2,E2:E10).
11. An employee’s hire date is in B2. Write a formula using DATEDIF to calculate their tenure in complete months (not years) as of today.
View Answer
=DATEDIF(B2,TODAY(),”M”)12. A subscription report needs to flag any subscription whose renewal date (in B2) falls within the next 7 days, including today. Write an IF formula for this, returning “Renew Soon” or “OK.”