Forum Discussion
Solved
Nested if statement based on dates for New Column
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!
Try using the datediff function instead... Something similar to this:
Weeks_After_Start_Date = DATEDIFF("1/30/2023", 'Table'[Completed Date], WEEK)
