Lessons Lesson 77

Date Arithmetic: Subtracting Dates

About this lesson

Subtracting one date from another in Excel gives the number of days between them. Both are counts of days to begin with.

By the end you can

  • subtract two dates to get the days between them.
  • add days to a date and test whether a deadline was met.

Practises SUM DATEDIF TODAY

By Ali, Founder, Excel Lessons

The idea

There is no function for this, and there does not need to be one. Finished minus started is a plain subtraction. It crosses month and year boundaries without any help. The result is an ordinary number that can be compared, averaged and totalled.

The thing to decide before you write it is which question you are answering. A plain subtraction gives the days between two dates. Monday to Friday is four. Adding one gives the days the job took up. Monday to Friday is five. Both are reasonable, and both look normal in a column. A report that mixes the two is off by the number of rows in it. Billing by the day almost always wants the inclusive count. A duration or a lead time almost always wants the plain one.

The mistake to watch for

Not deciding which count you mean. A plain subtraction gives the days between two dates. Monday to Friday is four. Adding one gives the days the job took up, five. Both look normal in a column. A report that mixes them is off by the number of rows in it. Billing by the day wants the inclusive count. A duration wants the plain one.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

Finished minus started gives the days a job took.

In D2, type:

=C2-B2

That is how long the roof survey took.

D2
Row A B C D E F G
1 Job Started Finished Days Within SLA
2 Roof survey 45200 45204
3 Damp report 45210 45231
4 Boiler check 45244 45245
5
6 SLA days 10
7
8

Every step

  1. 1

    Finished minus started gives the days a job took. In D2, type: =C2-B2. That is how long the roof survey took.

    Hint. One cell minus another.

  2. 2

    Jobs must finish within the limit in B6. In E2, compare D2 to B6. TRUE means it met the limit.

    Hint. The days are already worked out in D2.

  3. 3

    Read =C3-B3 and say how long the damp report took.

    Hint. Subtract one date from the other.

  4. 4

    E4 should say whether the boiler check met the limit. It says FALSE, but the job took one day. Fix E4.

    Hint. Where is the service level written down?

  5. 5

    In D6, total D2:D4 with SUM.

    Hint. Three cells.

  6. 6

    Adding 1 counts both the first and last day. Read =C4-B4+1 and say what it returns for the boiler check.

    Hint. One day between, or two days inclusive?