example date 1: 1/1/2025 the closest monday is Dec 30.
example date 2: 1/1/2028 the closest monday is Jan 4 (I think that’s correct)
I’m trying to create a formula that figures this out. Sometimes the closest monday is before the date, sometimes after.
in 2028, first Mon is Jan. 3rd.
Given the year in A1 (a number like 2025):
=DATE(A1;1;4)-WEEKDAY(DATE(A1;1;4);3)
=LET(Jan4th;DATE(A1;1;4);Jan4th-WEEKDAY(Jan4th;3))Next try: =DATE(A1;1;4)-WEEKDAY(CURRENT();3) works with any version of Open/LibreOffice, but not with Excel.
AI says :
=IF(WEEKDAY(TODAY(),2) <= 3, TODAY() - WEEKDAY(TODAY(),2) + 1, TODAY() + 7 - WEEKDAY(TODAY(),2) + 1)
change today() with 01.01…
Hallo
=MROUND(DATE(A1 ; 1 ; -1 );7) + 2
Just note that this nice hack would only work with the default epoch.
of course!