Nested if statement based on dates for New Column

[medwards]

Hope you can kindly assist. I'm trying to write a nested if statement for a new column I created, but I'm having trouble with the syntax. My new column is called "WEEK #" where it is the number of weeks after the start date (Jan 30, 2023).

WEEK # = IF(

'TABLE'[Completed Date] >= DATE(2023,1,30) AND <= DATE(2023,2,4),1,

(IF('TABLE'[Completed Date] >= DATE(2023,2,5) AND <= DATE(2023,2,11),2,

(IF('TABLE'[Completed Date] >= DATE(2023,2,12) AND <= DATE(2023,2,18)),3,

(IF('TABLE'[Completed Date] >= DATE(2023,2,19) AND <= DATE(2023,2,25)),4,

(IF('TABLE'[Completed Date] >= DATE(2023,2,26) AND <= DATE(2023,3,4)),5),

ELSE " ")))))

If there's an easier way to do this, I'm open to other ideas. Thanks!

[medwards]

Thank you! That is so much easier 🙂

[Syk · Accepted solution]

Try using the datediff function instead... Something similar to this:

Weeks_After_Start_Date = DATEDIFF("1/30/2023", 'Table'[Completed Date], WEEK)