Forum Discussion
Can someone explain in layman's terms how this formula works?
Hi All,
I've used this formula to develop a Gant Chart. The formula works perfectly, however, I get lost in trying to understand it.
=IF(MONTH(C2-WEEKDAY((C2),2)+1)<MONTH(C2),(C2-28-DAY(C2)+7)-WEEKDAY((C2-DAY(C2)+7),2)+1,(C2-DAY(C2)+7-WEEKDAY((C2-DAY(C2)+7),2)+1))
Could someone please give me a plain English description of how this formula works, so I can explain it if asked?
3 Replies
C2-WEEKDAY((C2),2)+1 returns the most last Monday on or before the date in C2.
For example, on today (Friday the 1st of July, 2022), it returns Monday, the 27th of June, 2022.
MONTH(C2-WEEKDAY((C2),2)+1)<MONTH(C2) compares the month of that Monday to the month of C2 itself.
If that Monday is in the previous month, as for today, the formula returns (C2-28-DAY(C2)+7)-WEEKDAY((C2-DAY(C2)+7),2)+1 (we'll come back to that), else it returns (C2-DAY(C2)+7-WEEKDAY((C2-DAY(C2)+7),2)+1).
The latter, (C2-DAY(C2)+7-WEEKDAY((C2-DAY(C2)+7),2)+1), is the first Monday of the month.
The former, =(C2-28-DAY(C2)+7)-WEEKDAY((C2-DAY(C2)+7),2)+1 is 28 days before that, i.e. the Monday 4 weeks before the first Monday of the month of C2. Depending on the month, that can be the 1st or 2nd Monday of the previous month.
So: if the last Monday on or before the date in C2 falls in the previous month, the formula returns the date 4 weeks before the first Monday in the month of C2.
Otherwise, it returns the first Monday in the month of C2.
There is one exception: for a date in January, the month of the last Monday on or before the date in C2 will never be less than the month of C2, since 12 is not less than 1, so you'll always get the first Monday in January.
I cannot tell, of course, whether that is by design or a mistake.
HansVogelaar firstly, thank you so much for your time and knowledge it makes it much clearer.
Secondly, you highlighted the mistake in January. How would I correct that?
Or, would there be an easier formula/function to insert to show the date?
Here is a corrected and slightly shorter formula:
=C2-DAY(C2)-WEEKDAY(C2-DAY(C2),3)+IF(MONTH(F2)<>MONTH(C2),-21,7)
