Jul 01 2022 04:18 AM
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?
Jul 01 2022 05:11 AM
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.
Jul 05 2022 02:08 AM - edited Jul 05 2022 02:10 AM
@Hans Vogelaar 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?
Jul 05 2022 03:20 AM
Here is a corrected and slightly shorter formula:
=C2-DAY(C2)-WEEKDAY(C2-DAY(C2),3)+IF(MONTH(F2)<>MONTH(C2),-21,7)