Excel Practice
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

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.

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

How many days did the roof survey take? Put the answer in D2.

To begin, type it exactly:

=C2-B2

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

    How many days did the roof survey take? Put the answer in D2. To begin, type it exactly: =C2-B2.

    Hint. One cell minus another.

  2. 2

    Jobs are meant to close within the number of days in B6. In E2, say whether the roof survey met that. TRUE or FALSE.

    Hint. The days are already worked out in D2.

  3. 3

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

    Hint. Two serials, subtracted.

  4. 4

    E4 is meant to say whether the boiler check met the service level. It says it did not, on a job that took one day. Repair it.

    Hint. Where is the service level written down?

  5. 5

    In D6, use SUM to total the days across all three jobs.

    Hint. Three cells.

  6. 6

    One caution. Read =C4-B4+1 and say what it returns for the boiler check.

    Hint. One day between, or two days inclusive?