Dates in reports: YEAR, MONTH, DATEDIF
Reports are almost always requested by month: how much was sold in March, how much in April. In an export, every order has a full date, but no separate month. And SUMIF compares the whole cell, so no date fits the condition "March": the cell with 3/14/2026 stores a day number, not the word "March". Here's what it looks like:
The March revenue should go in B11. It includes four orders, from March 2 to March 31.
Year, month and day from a date
Three functions each get one part of a date as a number. For the date 3/14/2026 in cell A5:
- YEAR(A5) returns the year 2026;
- MONTH(A5) returns the month number 3: January is 1, December is 12;
- DAY(A5) returns the day of the month 14.
So you can add a column with the month number next to the dates, and SUMIF will look for the number 3 in it:
If a date like 1/3/1900 shows up instead of the month number, the cell got a date format. This happens when a column is inserted next to dates. Give the cell the General format, and it shows the number 3 itself.
When an export covers several years, March 2025 and March 2026 have the same number 3. Then you add one more column with YEAR and calculate with SUMIFS using two conditions. If the month is in column C and the year in D, the March 2026 revenue is calculated like this:
=SUMIFS(B2:B9,C2:C9,3,D2:D9,2026)
Year and month columns are useful even without formulas: they make it easy to sort and filter orders.
Day of the week
On which days of the week do people order the most? WEEKDAY returns the number of the day of the week. Write 2 as the second argument: then Monday gets the number 1 and Sunday 7. Without this argument, Excel starts the week on Sunday.
With these numbers, you pick out weekends with a single condition ">5" in SUMIF.
Full years: DATEDIF
The difference between two dates gives the number of days. For years of service or age you need full years, and dividing days by 365 isn't accurate: because of leap years, the answer is off near the anniversary. DATEDIF counts the full years between dates:
=DATEDIF(B2,TODAY(),"y")
The earlier date comes first, the later one second, and the unit in quotes third:
- "y" counts full years;
- "m" counts full months;
- "d" counts days.
If you swap the dates, you get the #NUM! error.
As you type, Excel doesn't suggest DATEDIF in the list of functions: it was kept for compatibility with old spreadsheets. Type the full name, and the formula works.
Workdays: NETWORKDAYS
A task's deadline is usually counted in workdays. NETWORKDAYS(B2,C2) counts the days from the first date to the second, including both, and skips Saturdays and Sundays. The function doesn't know holidays by itself: you list them in separate cells and pass them as the third argument.