Updating Dates in Excel

Tygra_1983 

OK, here's that formula in column K: 

=IF(DATE(2021,MONTH($C$2),J6)>=$C$2,

DATE(2021,MONTH($C$2),J6),

DATE(2021,MONTH($C$2)+1,J6))

Other than the IF function (which is a simple IF in this case), there are only two other functions used:

DATE and MONTH

DATE works by creating an Excel serial number that represents the date. (FYI, Excel's serial numbers for dates begin with Sunday, Jan 1, 1900. The serial number for that date is 1. Days are added 1 by 1 since.) To generate a given date with DATE, you enter numbers representing a year, a month, and a day. 

MONTH takes a full date's serial number and tells you the number (1-12) of the month.

So putting that all together

• MONTH($C$2) takes today's date from cell C2 (or whatever date happens to be there) and returns the month
• and DATE(2021,MONTH($C$2),J6) takes the year 2021, the month from C2 (currently 6), and whatever number is in J6, to generate a date. In this case, because J6 contains a 20, the date is 6/20/21.
• the first conditional in the IF formula then just asks IF this newly generated date is greater than or equal to the date in cell C2 (i.e., does it still lie in the future?)
• THEN, the second clause in the IF function, since the condition is true, just displays that generated date 
• ELSE, the third clause in the IF function, since the primary condition is not true, i.e., the date has been passed, needs to generate the date in the succeeding month. So it uses MONTH to extract the current month and adds 1 to that -- MONTH($C$2)+1 -- and then uses DATE to put together that month, the year, and the day from J6, to show, for example, 7/20/21.

You know how best to start to figure these out for yourself? take a function like DATE and play with it. Put together different numbers (representing years, months and days, and refer to them in the DATE function. See what happens. Then embed a MONTH formula in there, and modify it using references that add or subtract values and see what happens to the date. It often is most helpful to go from the inside-out: in formulas like this, the MONTH function is embedded in the DATE, so first master MONTH, then build out to DATE.