Update cell if today's date within date range

0
votes

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,"")

  • This one deletes the entry if we are not in that date range, which I do not want
  • Is there a way to have the value of the cell be the value if the IF statement is false?

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

  • This returns a value of 0
1
excelexcel-formulaformulas
I'm afraid that's not possible. After the result of a formula is calculated, the only way (that I know about) to freeze the result is to copy/pasteValue. – A.S.H
@A.S.H I agree that it is not possible. Just going to manually update and look into VBA options in the future. – anonymous

1 Answers

1
votes

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.