IF x numbers of days have passed, do this.

H

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:
Sort by date Sort by votes
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Joe4 said:
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.
Click to expand...

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.

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

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=C2+D2[/TD]
[/TR]
[TR]
[TH]B7[/TH]
[TD="align: left"]=C7+D7[/TD]
[/TR]
[TR]
[TH]B12[/TH]
[TD="align: left"]=C12+D12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
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
Joe4 said:
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,"")
Click to expand...

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
Joe4 said:
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?
Click to expand...

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?
Click to expand...
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
Joe4 said:
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.
Click to expand...

I see.

Well, thanks a lot for your help, I really appreciate it. Another learning experience.
 
Upvote 0
You must log in or register to reply here.

Similar threads

SanjayGMusafir
  • Solved
Text function not returning correct month
Replies
7
Views
153
Alex Blakenburg
A
Vintage79
  • Solved
Count consecutive days and gaps between days
Replies
2
Views
214
Vintage79
Vintage79
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
782
mst3kr
M
HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
291
Jeffrey Mahoney
Jeffrey Mahoney
B
  • Question Question
Chart help - rainfall data
Replies
2
Views
249
JV0710
J

Forum statistics

Threads
1,223,704
Messages
6,173,984
Members
452,540
Latest member
haasro02

Share this page

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