Date format issued from date formula not recognized as date : message #DATE EXPECTED

Franckblt

Hello,

I face one difficulty trying to transform text in date using DATE formula

I want to find the first day of a week based on the week number & year number put in a cell

For instance in a cell I find "25-45" FOR Year= 2025 & Week= 45, so I look for the monday of this week that should be the 3rd of nov

Step 1 find first day of the year "01/01/25" using a cell called ([ref values]26, 2), containning value 01 and one formula for the year ="20" + LEFT([ref values]26, 2) =⇒ 2025

Step 2 transform text in date using T=DATE(VALUE([ref values]28), VALUE([ref values]29), VALUE([ref values]29)) =⇒ 01/01/2025

Step 3 : find one day of week 45 (1/1/25 +7x45) using =DATE(VALUE([ref values]28), VALUE([ref values]29), VALUE([ref values]29)) + 7 * VALUE([ref values]27)

=⇒ 12/11/25 the formula operates meaning the date is well interpreted

I use WEEKDAY on the same cell and i have the message #DATE EXPECTED

If I do the same in column where I forced date format none are recognized as date, like date are known as text by smartsheet

Thank's for your help

Paul Newcome

The WEEKDAY function outputs a number 1 - 7 to indicate which day of the week a referenced date is. It sounds like you are putting this WEEKDAY formula into a date type column when it should be in a text/number type column.