Excel track · Exercise 73 of 86 Paid
DAYS and simple date maths
Measure the distance between two dates in days, then treat the answer as the number it is.
Intermediate
Two projects with a start and an end date
Read-only data · screen 1 of 2 · answer in D2
Both dates are serials, so put the number of days between the escalation and its resolution into D2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Milestone | From | To | Days |
| 2 | Escalation | 45352 | 45366 | |
| 3 | Resolution | 45358 | 45366 |
Give the audit's length in whole weeks in D2, building the division on the days between the two dates.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Project | Start | End | Whole weeks |
| 2 | Audit | 45352 | 45415 | |
| 3 | Migration | 45366 | 45443 |
Reference answer
What it does
DAYS takes an end date and a start date, in that order, and returns the distance between them in days. A plain subtraction C2-B2 answers the same question; naming the function makes the direction explicit. The result is an ordinary number, so arithmetic applies to it.
The first screen measures an escalation from 1 March to 15 March 2024 and expects 14. The second counts an audit from 1 March to 3 May, which is 63 days, then divides by seven for 9 weeks. Swap the arguments and DAYS returns -14.
A serial is only a date because the workbook counts from 1899, so 45352 means 1 March 2024 by convention. Keep a day count formatted as a number: format 14 as a date and the cell claims 14 January 1900.
Syntax
=DAYS(end, start)Worked example
On Two projects with a start and an end date, =DAYS(C2,B2) returns 14.
=DAYS(C2,B2)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | A date argument is text that Excel cannot read as a date, so DAYS has no serial to subtract. |
#NUM! | An argument sits outside the range of serials this workbook can hold, so no distance can be computed. |
Negative days | The arguments are the wrong way round, so DAYS returns -14. |
At work
A service-level report that measures how long each ticket stayed open, then states the same figure in weeks.