Forum Discussion

TraderRef's avatar
TraderRef
Copper Contributor
Feb 19, 2020
Solved

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...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 19, 2020

    TraderRef 

    I guess in A4 it shall be

    =EOMONTH(TODAY(),-3)+1

     

TraderRef's avatar
TraderRef
Copper Contributor
Feb 19, 2020

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?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 20, 2020

TraderRef 

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.

Resources