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:
If I understand your question correctly, then I do not think that link is doing the same thing that you want, so I don't think you will be able to adopt those methods.
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
If I understand your question correctly, then I do not think that link is doing the same thing that you want, so I don't think you will be able to adopt those methods.

Yes, they are trying to do a different thing. But the original question asks if it's possible to combine OFFSET with LOOKUP, and someone replies that it is not, because LOOKUP produces a numeric value and offset works with cell ranges only. The only thing I need for my formula to work is to offset the value, and I found out in that thread that it's not possible. I know the other suggestions mentioned in the thread won't work.

Anyway, here is the example:

The table starts with B2= C2+D2. Every next row in Column A, I need the cell in Column A to evaluate if 5 days have passed since the date corresponding to the last value in Column A. If =5 days have passed, then calculate a new value for this cell in Column A, if not, =0.

ABCD
DATEACD
1-Jan19109
2-Jan1415
3-Jan510
4-Jan115
5-Jan1912
6-Jan261214
7-Jan147
8-Jan1711
9-Jan171
10-Jan63
11-Jan16313
12-Jan119
13-Jan181
14-Jan1815

<tbody>
</tbody>
if x days passed, then

Worksheet Formulas
CellFormula
B2=C2+D2
B7=C7+D7
B12=C12+D12

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:
Upvote 0
LOOKUP will return whatever is found in the cell, be it a number, text, or date.
OFFSET is a range function.

Regarding your question, a few questions about it, that may make this really easy.
Will B2 always be populated?
Will the dates in column A always be sequential with no gaps in between?
Will the "jump" always be exactly 5 days?

If all those conditions are true, then we know that we need that calculation every 5th row (B2, B7, B12, etc), so we can place this formula in B2 and copy down for all rows:
Code:
=IF(MOD(ROW(),5)=2,C2+D2,"")

If we always want a calculation in the starting row (in cell B2), and want it to sum every 5 days, but there may be gaps/jumps in the dates, then we can use this formula (put in B2 and copy down for all rows):
Code:
=IF(MOD(A2-$A$2,5)=0,C2+D2,"")
 
Upvote 0
LOOKUP will return whatever is found in the cell, be it a number, text, or date.
OFFSET is a range function.

Regarding your question, a few questions about it, that may make this really easy.
Will B2 always be populated?
Will the dates in column A always be sequential with no gaps in between?
Will the "jump" always be exactly 5 days?

If all those conditions are true, then we know that we need that calculation every 5th row (B2, B7, B12, etc), so we can place this formula in B2 and copy down for all rows:
Code:
=IF(MOD(ROW(),5)=2,C2+D2,"")

If we always want a calculation in the starting row (in cell B2), and want it to sum every 5 days, but there may be gaps/jumps in the dates, then we can use this formula (put in B2 and copy down for all rows):
Code:
=IF(MOD(A2-$A$2,5)=0,C2+D2,"")

Will B2 always be populated? Yes, B2 will always be populated
Will the dates in column A always be sequential with no gaps in between? Yes
Will the "jump" always be exactly 5 days? Yes

Woah, I hadn't seen the formula you suggested. It works, so far. Thank you. I will keep making tests.
 
Last edited:
Upvote 0
You are welcome. The keys are the ROW and MOD functions.

ROW() will return the row number of whatever cell the formula is placed in.
To see this, you can simply enter =ROW() in any cell and see what it returns.

The MOD function returns the remainder when one number is divided by another.
So, if we want to sum the values in cells B2, B7, B12, etc, we know that we want them in every 5th row starting with row 2.
So, we want to return them whenever we divide the row number by 5 and get a remainder of 2.
That is what the following part of the formula does: MOD(ROW(),5)=2

Does that make sense?
 
Upvote 0
You are welcome. The keys are the ROW and MOD functions.

ROW() will return the row number of whatever cell the formula is placed in.
To see this, you can simply enter =ROW() in any cell and see what it returns.

The MOD function returns the remainder when one number is divided by another.
So, if we want to sum the values in cells B2, B7, B12, etc, we know that we want them in every 5th row starting with row 2.
So, we want to return them whenever we divide the row number by 5 and get a remainder of 2.
That is what the following part of the formula does: MOD(ROW(),5)=2

Does that make sense?

Yes, the divisor will be the variable to be adjusted based on where the table starts then.

Any question though, what made you realize VBA wouldn't be needed? In which case would it have been needed?
 
Upvote 0
Any question though, what made you realize VBA wouldn't be needed? In which case would it have been needed?
Just knowledge of the different functions available and their capability (so experience, really). There aren't too many hard-and-fast rules regarding whether VBA is needed or not. Things like date stamps and loops and automation usually require VBA.

Quite honestly, formulas are a lot more powerful than people realize, and can do many complex things. A lot of things can be done by formulas, though sometimes the formulas may get so cumbersome, that a VBA approach may bre preferrable in those cases.
 
Upvote 0
Just knowledge of the different functions available and their capability (so experience, really). There aren't too many hard-and-fast rules regarding whether VBA is needed or not. Things like date stamps and loops and automation usually require VBA.

Quite honestly, formulas are a lot more powerful than people realize, and can do many complex things. A lot of things can be done by formulas, though sometimes the formulas may get so cumbersome, that a VBA approach may bre preferrable in those cases.

I see.

Well, thanks a lot for your help, I really appreciate it. Another learning experience.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

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