CODEWITHSUNDEEP
excelexcel-formulaformulas
anonymous
anonymous

Reputation: 576

Update cell if today's date within date range

I'm trying to create a formula that will update a cell with a value only if today's date falls into the month and year specified.

I have a value that constantly updates based off a table. I'd like the cells to the right of the dates (May-17, Jun-17, etc.) to update with the "Current" number of rows if today's date is within the date to the left.

For example, on May 31, 2017, the value in the cell to the right of "May-17" should be equal to the "Total Number of Rows" and starting on June 1, 2017, the May value will no longer update but the Jun-17 value will so as new projects are added, the May-17 value remains at 12 and the Jun-17 value increases.

enter image description here

The formula options I've played around with are:

=IF(MONTH(TODAY()) & YEAR(TODAY()) = MONTH(E7) & YEAR(E7),Total_Rows,"")

= SUMIF(E7,TEXT(E7,"mmm-yy") = TEXT(TODAY(),"mmm-yy"),Total_Rows)

Upvotes: 0

Views: 709

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

You can do it with your formula and enabling iterative calculations.

First, change the "" to refer to itself:

=IF(MONTH(TODAY()) & YEAR(TODAY()) = MONTH(E7) & YEAR(E7),Total_Rows,F6)

Next, go to File-->Options-->Formulas. check Enable Iterative calculation and put the Maximum Iterations to 1.

enter image description here

Now when it is not the month it will refer to itself in a circular formula that you are currently allowing and it will remain itself.

Upvotes: 1

Related Questions