CODEWITHSUNDEEP
exceldateif-statementvlookup
armadadrive
armadadrive

Reputation: 981

Comparing NOW() with a list of dates in Excel to return current period?

I have a spreadsheet that tracks my personal finances and projects income + expenses for 2 pay periods following the current period.

I want the date cells for the pay periods to update automatically if I open the spreadsheet and NOW() is > the current period. Then I would like that to cascade down to the next 2 date fields, adding 14 days to each one (or perhaps choosing the next two dates in a pre-defined range).

At first I thought of a VLOOKUP table, but I can't figure out the 'logic' to it because a VLOOKUP compares one thing and returns another.

My latest idea was to do the following:

//Where C11 is the cell with the last pay date in it
//My other two cells simply reference C11 and add 14 and 28 days, respectively.
=IF(INT(NOW()) > INT(C11), C11=C11+14, C11)

This is returning FALSE, so something is obviously wrong as I have a condition for False set.

Upvotes: 2

Views: 1162

Answers (1)

Jerry
Jerry

Reputation: 71578

You cannot update another cell from the formula of one cell.

For instance, you would put this formula in the two other cells referencing C11.

Since I have no visibility over your data, let's say the following:

  • Cell A1 contains the current period
  • Cell A2 contains the IF with NOW():
    =IF((INT(NOW()) > INT(A1),"Update","Don't update").
  • Cell A3 should contain another IF for the following 14 days after the current period;
    =IF(A2 = "Update", A1+14, "").
  • Cell A4 should contain yet another IF for the following 28 days after the current period;
    =IF(A2 = "Update", A1+28, "").

Or you can put the NOW() in both cells A3 and A4... which means you don't need cell A2.

  • Cell A1 contains the current period
  • Cell A3 should contain another IF for the following 14 days after the current period;
    =IF((INT(NOW()) > INT(A1), A1+14, "").
  • Cell A4 should contain yet another IF for the following 28 days after the current period;
    =IF((INT(NOW()) > INT(A1), A1+28, "").

Lastly, your currenty formula might be evaluating to TRUE and thus evaluates C11=C11+14. If C11 contains 12, it will become 12=12+14 which is false.

Upvotes: 1

Related Questions