Calculated Column – get first Monday on the next month

If you have a column Data1 – DateTime type column , only date – and you need to get first Monday of the following month, the needed formula in a calculated column will be:

=IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=1;DATE(YEAR(Data1);MONTH(Data1)+1;1+1);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=7;DATE(YEAR(Data1);MONTH(Data1)+1;1+2);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=6;DATE(YEAR(Data1);MONTH(Data1)+1;1+3);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=5;DATE(YEAR(Data1);MONTH(Data1)+1;1+4);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=4;DATE(YEAR(Data1);MONTH(Data1)+1;1+5);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=3;DATE(YEAR(Data1);MONTH(Data1)+1;1+6);DATE(YEAR(Data1);MONTH(Data1)+1;1)))))))

Depending on your regional settings, maybe is necessary to replace “;” with “,”

In my env., Sunday is the first day in the week

The conditions

WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=1

get which day will be the first day in the next month. ( 1 is for Sunday
7 Saturday, 6 Friday…. , 2 Monday )
Based on your env, change these values, but only the red values

This entry was posted in CalculatedColumn, Sharepoint Online. Bookmark the permalink.