Issue with an if

[Anonymous]

I have some pretty big nested Ifs' with which I assign the amount of working days to each month that's something like this:

if(assign_date_month = 'Jan', 16

,

if(assign_date_month = 'Feb',20

,

if(assign_date_month = 'Mar',23

,

if(assign_date_month = 'Apr',18

,

if(assign_date_month = 'May',22

,

if(assign_date_month = 'June',22

,

if(assign_date_month = 'July',21

,

if(assign_date_month = 'Aug',22

,

if(assign_date_month = 'Sept' and assign_date <= '11-09-17',6

,

if(assign_date_month = 'Sept' and assign_date >= '11-09-17',14

,

if(assign_date_month = 'Oct', 22

,

if(assign_date_month = 'Nov', 21

,

if(assign_date_month = 'Dec', 18

,if(assign_date_month = 'Jan' and Year(assign_date) = 2018,5)

)))))))))))))

The exception on September is due to the fact that September is split between two work cycles(Cycle 2 is until 11-09-17 and Cycle 3 is after 11-09-17), that I can manipulate via multibox. My problem is, apparently it doesn't work, because regardless if I select Cycle 2 or Cycle 3, the September value for working days is considered null.
TL;DR: I want my value for working days in September to change depending on what cycle I select(Cycle 2 and 3 in this case). Cycle 2 is until the date of 11-09-2017 and Cycle 3 is after the date of 11-09-2017. How can I do that, preferably in the nested Ifs?

[shiveshsingh]

Can you share some sample data?

What's the format for  assign_date? Try using Date function once

[Peter_Cammaert]

If assign_date = 11-09-17, you try to stuff two distinct values (6 and 14) in whatever field or table cell you are applying this expression to. That won't work. I think (according to your explanation) that the second if condition should omit the equal sign.

[Anonymous]

The format for assign_date is 'DD-MM-YY'

[Peter_Cammaert]

Also make sure that your default date format accepts 11-09-17 as a date string. Check global variable DateFormat for the correct format string.

[Anonymous]

So something like:

if(assign_date_month = 'Sept' and assign_date <= '11-09-17',6

,

if(assign_date_month = 'Sept' and assign_date > '11-09-17',14)
?

[Anonymous]

Also the Date Format is set correctly to DD-MM-YY

[Peter_Cammaert]

Now it depends on context (for which we don't get any details). If you now select september, then how many different dates will go into your expression? This expression will work just fine for september if you use it in a straight table in which assign_date is a dimension.

[Anonymous]

I am using it in a pivot table, with assign_date_month and area as dimensions, and the nested ifs are used in an expression to assign the working days for every month.

[Anonymous]

Managed to find a workaround by replacing the date comparisons with WildMatch(Concat(....