Working with dates

Calculate durations, deadlines and working days.

What you'll practise

Days between two dates
Compare with today's date
Get the year
Get the month
Get the day
Find the end of the month
Count working days

Days between two dates

Behind the scenes, Excel stores every date as a number: the count of days since 1900. That means you can subtract one date from another to get the days between them.

In cell E2, work out how many days the Design task takes: the end date in C2 minus the start date in B2.

Compare with today's date

TODAY returns the current date, and it updates by itself every day the file is opened. It takes no arguments, but it still needs its brackets: =TODAY().

Because dates are numbers, you can compare them. An earlier date is a smaller number.

In cell E2, show "Overdue" if the end date in C2 is before today, otherwise "On track".

Get the year

YEAR, MONTH and DAY pull one part out of a date. They're handy for grouping data by year or month.

=YEAR(date)

In cell E2, get the year of the date in B2.

Get the month

MONTH returns the month as a number from 1 for January to 12 for December.

In cell E2, use MONTH to get the month number of the date in C3.

Get the day

DAY returns the day of the month, from 1 to 31.

In cell E2, use DAY to get the day of the month from the date in C2.

Find the end of the month

EOMONTH finds the last day of a month. The second input says how many months ahead to look: 0 means the same month, 1 means next month.

=EOMONTH(date, months)

In cell E2, find the last day of the month for the date in B2.

Count working days

NETWORKDAYS counts the working days (Monday to Friday) between two dates, including both the start and the end date.

=NETWORKDAYS(start, end)

In cell E2, count the weekdays from the date in B2 to the date in C2.

Try it in a real spreadsheet

Every step checks your answer instantly and tells you exactly what to fix.

Start the lesson

Function guides

← Text tricks: REPT, CODE and CHAR Building and moving dates →