IF x numbers of days have passed, do this.

heretolearnexcel

New Member
Joined
Jan 22, 2019
Messages
26
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:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,843
Office Version
365
Platform
Windows
Something like this?
Code:
=IF(E7>=5,G7+H7,"")
 

heretolearnexcel

New Member
Joined
Jan 22, 2019
Messages
26
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,843
Office Version
365
Platform
Windows
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,"")
 

heretolearnexcel

New Member
Joined
Jan 22, 2019
Messages
26
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,843
Office Version
365
Platform
Windows
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).
 

heretolearnexcel

New Member
Joined
Jan 22, 2019
Messages
26
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,843
Office Version
365
Platform
Windows
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.
 

heretolearnexcel

New Member
Joined
Jan 22, 2019
Messages
26
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.
 

Forum statistics

Threads
1,086,084
Messages
5,387,709
Members
402,075
Latest member
COwen

Some videos you may like

This Week's Hot Topics

Top