Sure, I can help you with that! Here's a formula that will return the date of the first working day of the year based on the scenario you described:
- `=IF(WEEKDAY(DATE(YEAR(A1),1,1))=2,DATE(YEAR(A1),1 ,8),DATE(YEAR(A1),1,1+MOD(9-WEEKDAY(DATE(YEAR(A1),1,1)),7)))`
Let me break it down for you:
-
WEEKDAY(DATE(YEAR(A1),1,1)) returns the day of the week (as a number, where 1 is Sunday and 7 is Saturday) for January 1st of the year entered in cell A1.
- If January 1st is a Monday (i.e. the result of the above formula is 2), then the first working day is January 8th. This is handled by the first part of the
IF statement:
IF(WEEKDAY(DATE(YEAR(A1),1,1))=2,DATE(YEAR(A1),1,8 ),...)
- If January 1st is not a Monday, then we need to calculate the date of the first Monday. This is done using the second part of the
IF statement:
...DATE(YEAR(A1),1,1+MOD(9-WEEKDAY(DATE(YEAR(A1),1,1)),7)))
-
MOD(9-WEEKDAY(DATE(YEAR(A1),1,1)),7) calculates the number of days between January 1st and the next Monday (i.e. how many days we need to add to January 1st to get to the first Monday). The
MOD function ensures that we get a number between 0 and 6 (inclusive), since we only need to add up to 6 days to get to the next Monday.
- Finally, we add the number of days calculated above to January 1st to get the date of the first working day of the year.
Let me know if you have any questions or if there's anything else I can do for you.