Formula ? Change on 15th of every month

[zone709]

HI,

Trying to see if this possible.


I have so many different sheets. I need something to change the 15th of every month

Right now i have this

=Sheet2!C4

So on November 15th i need it to pull this

=Sheet2!C5

Dec 15th

=Sheet2!C6

Only this change would be the number. Possible?

 

[Finalfight40]

Do you need it to continue past December onto the next year?

 

[Finalfight40]

Try this:

=OFFSET(Sheet2!C4, MOD(IF(DAY(TODAY())>=15, MONTH(TODAY())-10, MONTH(TODAY())-10-1), 12)+(IF(MONTH(TODAY())<10, YEAR(TODAY())-2018-1, YEAR(TODAY())-2018))*12, 0)

Let me know if it doesn't work.

 

[zone709]

yes going on till i actually just delete it myself. Its principal numbers for that will change every month for years

 

[Finalfight40]

I noticed you looked at the reply as i posted it. I made 1 change since then so recopy and paste.

To test it all i done was replace Today() to any cell and type in some random future dates. See if it gives the results you wanted.

 

[zone709]

hi thanks it looks like its working. I guess the next month on the 15th ill know. One question if i dont get to add this formula on the sheets today and i do it tomorrow. Will it throw the formula off from working ?

 

[Finalfight40]

Should be fine whenever you add it to your spreadsheet.

 

[zone709]

ok great thanks a lot. I sometimes keep forgetting to move the formula every month as I have so many sheets. This is a good reminder thanks again

 

[Finalfight40]

You are welcome.

Happy to help.

 

[steve the fish]

Heres another way:

=INDEX(Sheet2!C:C,4+DATEDIF(DATE(2018,10,15),TODAY(),"m"))