Forum Discussion
Problem Determining Specific Dates with Formulas
We have a spreadsheet that was inherited from someone who retired. It helps us determine the patching schedules for Microsoft servers at our location.
The columns are Month, 2nd Tuesday, Wed Rd1, Sun Rd1, Wed Rd2, Sun Rd2, Wed Rd3 and Sun Rd3.
In the first column, Month, cell A4 gives us Dec 2019 by using the formula =EOMONTH(TODAY(),-2). The subsequent cells in that column are using the formula =EOMONTH(A4,0)+1 (Jan 2020), =EOMONTH(A5,0)+1 (Feb 2020), etc. all the way through Jan 2023, by which point I should be retired.
In the second column, 2nd Tuesday, cell B4 gives us Tue 01/07/2020 using the formula =A4+MOD(3-WEEKDAY(A4),7)+7, however, we expect to see Tue 12/10/2019. The subsequent cells in that column do give the correct dates.
In the third column, Wed Rd1, cell C4 gives us Wed 01/08/2020 using the formula =A4-MOD(A4+3,7)+14, however, we expect to see Wed 12/11/2019. The subsequent cells in that column do give the correct dates.
In the fourth column, Sun Rd1, cell D4 gives us Sun 01/12/2020 using the formula =C4+4, however, we expect to see Sun 12/15/2019. The subsequent cells in that column do give the correct dates.
The other columns are calculated using the same logic as found in D4, =C4+7, =D4+7, =E4+7 and =F4+7. However, they still show dates in January 2020, whereas The subsequent cells in those columns do give the correct dates.
With all the other cells referencing cell A4, we need to get that formula correct so the cells in row 4 reflect the 2019 dates and all of the other cells continue to be correct. Again, A4 is set as =EOMONTH(TODAY(),-2).
Could anyone tell me what is wrong with this spreadsheet that is causing the cells in row 4, except cell A4, yet all of the other cells reflect correct dates? I have attached the spreadsheet if it helps.
3 Replies
SergeiBaklan, your solution absolutely did it!! Thank you so much!!
Both formulas reflected Dec 2019, which was supposed to happen. But, prior to changing A4 to your formula, the subsequent cells in that row showed January dates. After I changed the formula in A4 to your suggestion, =EOMONTH(TODAY(),-3)+1, it still showed Dec 2019, but the other cells in that row reflected the accurate Dec 2019 dates.
Do you have anything to share that might help me understand why that formula worked for the other cells in that row while the previous formula, =EOMONTH(TODAY(),-2), did not?
To calculate patching dates correctly formula for the month shall return first date of the month.
=EOMONTH(TODAY(),-2) returns Dec 31, 2019 (last day of the month two months ago)
=EOMONTH(TODAY(),-3)+1 returns Dec 01, 2019 (last day of the month three months ago plus one day, i.e. first date of the next month)
Formatted as mmm yyyy both formulas shows Dec 2019, but actually the return different dates in Dec 2019.
