Weeks of 2023 calculating incorrectly

Options

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


Answers

  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    @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
    Hollie Green ✭✭✭✭✭✭
    edited 01/09/23
    Options

    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
    Samuel Mueller Overachievers
    Options

    @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
    Hollie Green ✭✭✭✭✭✭
    Options

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

  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    @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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!