## Calculating the Number of Days Between Two Dates in Excel

The datedif function can be used in Microsoft Excel to calculate the number of days between 2 different dates. E.g. suppose a request has been submitted for work to be completed and I want to calculate, for a not yet implemented request, how many days have elapsed since the request was approved.

I have an Excel spreadsheet with the request number in column A and the date the request was submitted in column B. The dates the request was approved and then implemeted are in columns C and D respectively. I want the number of days that have elapsed since the request was approved to appear in column E. I want that value updated every time I open the spreadsheet so that the elapsed number of days always matches the number of days from the approval date until today.

 Request Submitted Approved Implemented Days in Approved State A B C D E 1 2 09-0010 8/20/2009 8/20/2009 26

The forumla I would put in cell E2 would be ```=DATEDIF(C2,Today(),"d") ``` to display the number of days that have elapsed. I could also use `=DATEDIF(C2,Now(),"d")`. If today is September 15, 2009, then the result in cell E2 will be `26`.

The syntax for the `DATEDIF` function is as follows:

`=DATEDIF(Date1, Date2, Interval)`

Where:

Date1 is the first date,
Date2 is the second date,
Interval is the interval type to return.

If Date1 is later than Date2, DATEDIF will return a #NUM! error. If either Date1 or Date2 is not a valid date, DATEDIF will return a #VALUE error.
The Interval value should be one of the following:

 Interval Meaning Description m Months Complete calendar months between the dates. d Days Number of days between the dates. y Years Complete calendar years between the dates. ym Months Excluding Years Complete calendar months between the dates as if they were of the same year. yd Days Excluding Years Complete calendar days between the dates as if they were of the same year. md Days Excluding Years And Months Complete calendar days between the dates as if they were of the same month and same year.

If Interval is not one of the items listed in above, DATEDIF will return a #NUM error.

In the above example, I'm presuming the request is not yet implemented. But I'd really like to test the value in column D to make that determination. If there is no value there, the request has not yet been implemented. If there is a date there, then I don't want to calculate the days the request was in the approval state from the date it was approved until today, but rather from the date it was approved until the date the work was completed, i.e. the date in the "implemented" column. For that determination, I'll need to use an `if-then-else` calculation.

If I wanted to calculate the number of elapsed days between the date a request was approved and today when cell `D2` is blank, but calculate the number of days betwen the approval date and the implementation date when `D2` has an implementation date, I could use `=IF(ISBLANK(D2),DATEDIF(C2,TODAY(),"d"),DATEDIF(C2,D2,"d"))`. For the `IF` function, the test condition is `ISBLANK(D2)`. If cell `D2` is empty, i.e. blank, then, for the "true" condition, `DATEDIF(C2,TODAY(),"d")` is used. If cell `D2` is not blank, then, for the "false" condition `DATEDIF(C2,D2,"d)` is used.

If there are two requests, `09-0010` and `09-0011`, where `09-0010` has no been implemented yet, but `09-0011` was implemented on 2009-09-04, the results would be as follows:

 Request Submitted Approved Implemented Days in Approved State A B C D E 1 2 09-0010 8/20/2009 8/20/2009 26 3 09-0011 8/20/2009 8/20/2009 9/4/2009 15

The syntax for the `IF` function is as follows:

`IF(logical_test,value_if_true,value_if_false)`

Where:

Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.
Value_if_true is the value that is returned if logical_test is TRUE.
Value_if_false is the value that is returned if logical_test is FALSE.

In the example above, the formula can be copied from the top entry in line 2 down to subsequent entries, such as the one in line 3, by highlighting cell `E2` then, while holding the left mouse button down, drag the mouse downwards to cover all pertinent cells in column E to highlight them as well, then hit `Ctrl-D`, i.e. the `Ctrl` and `D` keys simultaneously, to copy the formula downwards to subsequent lines. Excel will update the cell values in the formula appropriately as you copy the formula downwards. I.e., it will insert the formula `=IF(ISBLANK(D3),DATEDIF(C3,TODAY(),"d"),DATEDIF(C3,D3,"d"))` in cell `E3`.

I also need to allow for the case where a request is not yet approved, i.e. where a cell in column `C` is empty. So I will need to nest `if` conditions and use the formula `=IF(ISBLANK(C2),0,IF(ISBLANK(D2),DATEDIF(C2,TODAY(),"d"),DATEDIF(C2,D2,"d")))` in column `E`.

 Request Submitted Approved Implemented Days in Approved State A B C D E 1 2 09-0010 8/20/2009 8/20/2009 26 3 09-0011 8/20/2009 8/20/2009 9/4/2009 15 4 09-0012 8/20/2009 0

If I also want to change the color of the number to red whenever the number of days in the approved state is greater than 5, I can use "conditional formatting". The steps to do so are as follows:

1. Highlight all the cells in column `E` underneath "Days in Approved State" to highlight the entire column, except the column heading.
2. Select Format.
3. Select Conditional formatting.

4. Select "greater than" for the condition and put `5` in the value field.
5. Click on the Format button.
6. Select red for the font color.

7. Click on OK.
 Request Submitted Approved Implemented Days in Approved State A B C D E 1 2 09-0010 8/20/2009 8/20/2009 26 3 09-0011 8/20/2009 8/20/2009 9/4/2009 15 4 09-0012 8/20/2009 0 5 09-0012 8/20/2009 8/24/2009 22 6 09-0012 8/20/2009 8/24/2009 8/25/2009 1

References:

1. DATEDIF Worksheet Function
Pearson Software Consulting, LLC - Your Source for Excel

Justdeals Daily Electronics Deals

Created: Tuesday September 15, 2009