Cell to equal a future date when date arrives and remains after the date passes

[dallen8028]

Attempting to have Cell equal a future date when that day arrives and remains that date after the date passes. i.e. first of each month. This date cell will trigger other formulas within the spreadsheet when that date cell is populated with the 1st of the month. I will have 12 cells (1st of each month) that need to populate with the that date. Does this make sense? Thank you in Advance.

 

[Joe4]

If you hard-code the value of a future date, that hard-coded value will never change (and there should be no issue).
However, I am guessing that you left off an important detail, and that this value is probably derived from a formula. Is that correct?

If so, a cell can either contain a hard-coded value or a formula, but never both at the same time.
So to do what you want either requires:
- the use of a helper cell (i.e. that holds the hard-coded value of the future data you do not want to exceed)
or
- the use of VBA
Are either of those an option?

Supposing it is a formula, it would be helpful to see exactly what the formula is, and what cell it resides in.

 

[dallen8028]

This would be a screenshot of the area of question. The formula would change to the 1st of each month in the next cell to the right. What am I doing wrong? Formulas are better than VBA for me. Thanks Again.

 

[Joe4]

If I am understanding your correctly, place this formula in cell E2 and copy across to cell P12:

=IF(MONTH($B$2)>=(COLUMN()-4),DATE(YEAR($B$2),COLUMN()-4,1),"")

 

[dallen8028]

Thank you Joe4, I copied and pasted as directed. It populated the first cell as needed. I am sure this will populate the following cells appropriately. Thank again

 

[Joe4]

Thank you Joe4, I copied and pasted as directed. It populated the first cell as needed. I am sure this will populate the following cells appropriately. Thank again

You are welcome.
You can test the other cells by changing the date in cell B2.
If the intention in cell B2 is to always show the current date, you can put this formula in cell B2:

=TODAY()

but you can change the date in cell B2 for testing purposes.