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.
| A | B | C | D | E | |
| 1 | Request | Submitted | Approved | Implemented | Days in Approved State |
|---|---|---|---|---|---|
| 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:
| A | B | C | D | E | |
| 1 | Request | Submitted | Approved | Implemented | Days in Approved State |
|---|---|---|---|---|---|
| 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.
| A | B | C | D | E | |
| 1 | Request | Submitted | Approved | Implemented | Days in Approved State |
|---|---|---|---|---|---|
| 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:
E underneath "Days in
Approved State" to highlight the entire column, except the column heading.
5 in the value
field.
| A | B | C | D | E | |
| 1 | Request | Submitted | Approved | Implemented | Days in Approved State |
|---|---|---|---|---|---|
| 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:
Created: Tuesday September 15, 2009