IF x numbers of days have passed, do this.

heretolearnexcel

Board Regular
Joined
Jan 22, 2019
Messages
58
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:

1DATEDays passedACD
21-Jan19109
32-Jan11415
43-Jan2510
54-Jan3115
65-Jan41912
76-Jan5261214
87-Jan1147
98-Jan21711
109-Jan3171
1110-Jan463
1211-Jan516313
1312-Jan1119
1413-Jan2181
1514-Jan31815

<tbody>
</tbody>

Any ideas on how to achieve this?

Thanks.
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
DateDays Passed
1-Jan
2-Jan1
3-Jan2
4-Jan3
5-Jan4
6-Jan5
7-Jan1
8-Jan2
9-Jan3
10-Jan4
11-Jan5
12-Jan1
13-Jan2
14-Jan3

<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.
 
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,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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