# IF x numbers of days have passed, do this.

#### heretolearnexcel

##### New Member
Hi,

I have a Table with days like this:

I need a formula that if x number of days have passed it will do something. For example, if 5 days have passed since last time, "sum these two values"

column A= C+D, if 5 days have passed since last time there was a value in the column:

 1 DATE Days passed A C D 2 1-Jan 19 10 9 3 2-Jan 1 14 15 4 3-Jan 2 5 10 5 4-Jan 3 11 5 6 5-Jan 4 19 12 7 6-Jan 5 26 12 14 8 7-Jan 1 14 7 9 8-Jan 2 17 11 10 9-Jan 3 17 1 11 10-Jan 4 6 3 12 11-Jan 5 16 3 13 13 12-Jan 1 1 19 14 13-Jan 2 18 1 15 14-Jan 3 18 15

<tbody>
</tbody>

Any ideas on how to achieve this?

Thanks.

Last edited:

#### Joe4

Something like this?
Code:
``=IF(E7>=5,G7+H7,"")``

#### heretolearnexcel

##### New Member
Something like this?
Code:
``=IF(E7>=5,G7+H7,"")``
So I would have to necessarily include a kind of helper column, "days passed". But in that case I would still have the question of what formula to use to automatically calculate the the number of days passed at each date.
 Date Days Passed 1-Jan 2-Jan 1 3-Jan 2 4-Jan 3 5-Jan 4 6-Jan 5 7-Jan 1 8-Jan 2 9-Jan 3 10-Jan 4 11-Jan 5 12-Jan 1 13-Jan 2 14-Jan 3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><tbody>
</tbody>

I was actually trying to figure out a way to do it by using some sort of condition and subtracting directly the dates from the "day" column, I don't know if that's possible.

#### heretolearnexcel

##### New Member
from the DATE column, not "day". Sorry.

#### Joe4

No, you do not need a helper column.
If your date is in column D, you could do a formula like:
Code:
``=IF((TODAY()-D7)>=5,G7+H7,"")``

#### heretolearnexcel

##### New Member
No, you do not need a helper column.
If your date is in column D, you could do a formula like:
Code:
``=IF((TODAY()-D7)>=5,G7+H7,"")``
Are there any alternatives that don't use the TODAY function? I need to calculate it for past dates, not every new day.

#### Joe4

Are there any alternatives that don't use the TODAY function? I need to calculate it for past dates, not every new day.
It is important to understand exactly what that part of the formula is doing:
TODAY()-D7
It is taking the past date in cell D7, and seeing how many days it is in the past from today.

If you do not want to checked the number of elapsed days from today, but rather from some other date, that is fine, as long as you have that "other" date stored somewhere in the workbook.
If it is in another cell, say A1, you can simply use that instead of TODAY(), i.e.
\$A\$1-D7
so the overall formula would look like:
Code:
``=IF((\$A\$1-D7)>=5,G7+H7,"")``
(note, I am assuming that the date is only in the sheet once, in A1, so I am locking down the range reference with "\$" signs, so it will work if you copy the formula down rows).

#### heretolearnexcel

##### New Member
I think the issue is that I didn't specify something. I need the number of days calculated starting from the last time there was a value in the sum column. So the logic needs to be something like: is the number of days that have passed since the last time there was a value in column "A" equal to 5?

#### Joe4

I think the issue is that I didn't specify something. I need the number of days calculated starting from the last time there was a value in the sum column. So the logic needs to be something like: is the number of days that have passed since the last time there was a value in column "A" equal to 5?
Excel can either have a formula, or a hard-coded value in a cell, and one cannot turn to the other without it being done manually, or by VBA.
So I think you answer requires VBA. You would need something like a date stamp to capture when values are placed in certain cells.

If you are open to a VBA solution, we can probably help you, but you will need to be VERY specific in the details, being sure to indicate exactly what ranges are involved, and when/how things should happen.

#### heretolearnexcel

##### New Member
Excel can either have a formula, or a hard-coded value in a cell, and one cannot turn to the other without it being done manually, or by VBA.
So I think you answer requires VBA. You would need something like a date stamp to capture when values are placed in certain cells.

If you are open to a VBA solution, we can probably help you, but you will need to be VERY specific in the details, being sure to indicate exactly what ranges are involved, and when/how things should happen.
I tried combining these formulas:

LOOKUP(2,1/(ISNUMBER(A1:A100)),A1:A100)

=IFERROR(INDEX(B12:F12,MATCH(TRUE,B12:F12<>"",0)),"")

with offset, but I get an error. I found this thread a thread that mentions: "The <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Reference</code> is the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">OFFSET</code> function refers to a Range object (a cell). The result of your <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Lookup</code> function is a numeric value, in this case 5. You can't <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">OFFSET</code> a numeric value."

https://stackoverflow.com/questions/28329954/combining-lookup-and-offset

So I'm guessing that happens with both LOOKUP and INDEX_MATCH, and the only solution is VBA...

I will work on an example with all the details to see what answers I can get.