Weeks of 2023 calculating incorrectly

Hollie Green

Using the same formula I used for 2022.

=IF(WEEKDAY(DATE(Year@row, 1, 1) + Week@row * 7) = 2, DATE(Year@row, 1, 1) + Week@row * 7, (DATE(Year@row, 1, 1) + Week@row * 7) - (WEEKDAY(DATE(Year@row, 1, 1) + Week@row * 7) - 2))

Results for 2022 come back correct for week 1 but for 2023 comes back with the date that should be for week 2

Samuel Mueller

@Hollie Green what are you trying to achieve? That formula you have written is returning the correct date based on how it's written, so you would need to change the formula.

Hollie Green

Week 1 of 2023 should return 1/2/23 but it is returning 1/9/23. It worked correctly for 2022 bringing back the date of 1/3/22.

If I change the formula it will bring back the incorrect dates for 2022.

Samuel Mueller

@Hollie Green try this

=if(weekday(date(year@row,1,1))=2,date(year@row,1,1), if(weekday(date(year@row,1,2))=2,date(year@row,1,2), if(weekday(date(year@row,1,3))=2,date(year@row,1,3), if(weekday(date(year@row,1,4))=2,date(year@row,1,4), if(weekday(date(year@row,1,5))=2,date(year@row,1,5), if(weekday(date(year@row,1,6))=2,date(year@row,1,6), if(weekday(date(year@row,1,7))=2,date(year@row,1,7)))))))

Hollie Green

That formula returns the same date for all the weeks of the year.

Samuel Mueller

@Hollie Green I'm sorry I forgot about that part of your formula. add + ((Week@row - 1) * 7) to the end like below

=IF(WEEKDAY(DATE(Year@row, 1, 1)) = 2, DATE(Year@row, 1, 1), IF(WEEKDAY(DATE(Year@row, 1, 2)) = 2, DATE(Year@row, 1, 2), IF(WEEKDAY(DATE(Year@row, 1, 3)) = 2, DATE(Year@row, 1, 3), IF(WEEKDAY(DATE(Year@row, 1, 4)) = 2, DATE(Year@row, 1, 4), IF(WEEKDAY(DATE(Year@row, 1, 5)) = 2, DATE(Year@row, 1, 5), IF(WEEKDAY(DATE(Year@row, 1, 6)) = 2, DATE(Year@row, 1, 6), IF(WEEKDAY(DATE(Year@row, 1, 7)) = 2, DATE(Year@row, 1, 7)))))))) + ((Week@row - 1) * 7)