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:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Something like this?
Code: 
=IF(E7>=5,G7+H7,"")
 
Upvote 0
Joe4 said:
Something like this?
Code: 
=IF(E7>=5,G7+H7,"")
Click to expand...

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
Joe4 said:
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,"")
Click to expand...

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

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
You must log in or register to reply here.

Similar threads

N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
111
Peter_SSs
Peter_SSs
SanjayGMusafir
  • Solved
Text function not returning correct month
Replies
7
Views
255
Alex Blakenburg
A
Vintage79
  • Solved
Count consecutive days and gaps between days
Replies
2
Views
218
Vintage79
Vintage79
Lacan
  • Question Question
How to filtered/extract names and calculate difference between values?
2
Replies
15
Views
216
Lacan
Lacan
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
873
mst3kr
M

Forum statistics

Threads
1,224,739
Messages
6,180,674
Members
452,993
Latest member
FDARYABEE

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