Weeks Out Calculation based on Nested IF Statement

cstan

I'm having trouble having my weeks out calculation work based on the results of a Nested IF Statement. The If Statement works if I use quotation marks around the results which need to display a date. The problem is that putting the quotes around the results converts the number (in my case a date) into text and no longer feeds the second formula of calculating weeks out. My nested IF Statement which posts results to a column entitled "Market Start Date"

=IF([AW Market]1 = "Europe", "3/19/18", IF([AW Market]1 = "LATAM", "2/12/2018", IF([AW Market]1 = "APAC", "7/30/18", IF([AW Market]1 = "Asia", "5/14/18", IF([AW Market]1 = "New York", "10/1/2018", 0)))))

The weeks out formula is below:

=-ROUND(([Start Date]1 - [Market Start Date]1) / 7, 0)

I assume I need to modify the if statement such that it produces a result that is a date and that the column property would need to be defined as a date as well? Thanks for the help anyone!

 

Cstan

rjudenberg

Correct, [Market Start Date] should be a date column

Instead of your date literals, use the DATE function

so "3/19/18" becomes DATE(2018,3,19)