IF x numbers of days have passed, do this.

heretolearnexcel

Board Regular
Joined
Jan 22, 2019
Messages
61
Office Version
  1. 365
Platform
  1. Windows
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]DATE[/TD]
[TD="align: center"]Days passed[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]2-Jan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]4-Jan[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]5-Jan[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]6-Jan[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]7-Jan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]8-Jan[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]9-Jan[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]10-Jan[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]11-Jan[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]12-Jan[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]13-Jan[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]14-Jan[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas on how to achieve this?

Thanks.
 
Last edited:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Something like this?
Code:
=IF(E7>=5,G7+H7,"")
 
Upvote 0
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.[TABLE="class: grid, width: 500"]
<colgroup><col style="width: 25pxpx"><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD]Date[/TD]
[TD]Days Passed[/TD]
[/TR]
[TR]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]2-Jan[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]4-Jan[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]5-Jan[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]6-Jan[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]7-Jan[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8-Jan[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9-Jan[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]10-Jan[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]11-Jan[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]12-Jan[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]13-Jan[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]14-Jan[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
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,"")
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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."

Link to thread:
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.
 
Upvote 0

Forum statistics

Threads
1,223,703
Messages
6,173,973
Members
452,540
Latest member
haasro02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top