Expression help, super long if statement. Feels like i am doing something wrong

AppSheet AppSheet Q&A
,
1

I am super stuck on a problem, which is more excel than anything else. Basically I have spread sheet that I would like to select the proper hourly rate based on the date selected, if that date is a weekday, saturday or sunday, if the day is before or after the 23rd of each month, and in three different sections of the year.

ANY(SELECT(oldACMSrates[Apeak2], AND([_THISROW].[start_date] <= “05/28/2022”, DAY([_THISROW].[start_date]) >= 23, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[Apeak1], AND([_THISROW].[start_date] > “05/28/2022”, [_THISROW].[start_date] <= “09/30/2022”, DAY([_THISROW].[start_date]) < 23, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[Apeak2], AND([_THISROW].[start_date] > “05/28/2022”, [_THISROW].[start_date] <= “09/30/2022”, DAY([_THISROW].[start_date]) >= 23, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[Apeak1], AND([_THISROW].[start_date] > “10/01/2022”, DAY([_THISROW].[start_date]) < 23, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[Apeak2], AND([_THISROW].[start_date] > “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[Apeak2], OR([_THISROW].[start_date] < “05/29/2022”, DAY([_THISROW].[start_date]) >= 23, WEEKDAY([_THISROW].[start_date]) <> 1, [_THISROW].[manpower] = [manpower]),AND([_THISROW].[start_date] < “05/29/2022”, DAY([_THISROW].[start_date]) >= 23, WEEKDAY([_THISROW].[start_date]) <> 7, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[Bpeak1], OR([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) < 23, WEEKDAY([_THISROW].[start_date]) <> 1, [_THISROW].[manpower] = [manpower]), AND([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) < 23, WEEKDAY([_THISROW].[start_date]) <> 7, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[Bpeak2], OR([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, WEEKDAY([_THISROW].[start_date]) <> 1, [_THISROW].[manpower] = [manpower]), AND([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, WEEKDAY([_THISROW].[start_date])<> 7, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[Apeak1], OR([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) < 23, WEEKDAY([_THISROW].[start_date])<> 1, [manpower] = [manpower]), AND([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) < 23, WEEKDAY([_THISROW].[start_date])<> 7, [manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[Apeak2], OR([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, WEEKDAY([_THISROW].[start_date])<> 1, [_THISROW].[manpower] = [manpower]), AND([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, WEEKDAY([_THISROW].[start_date])<> 7, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[OTAsat1], AND([_THISROW].[start_date] < “05/29/2022”, DAY([_THISROW].[start_date]) < 23, WEEKDAY([_THISROW].[start_date])= 7, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[OTAsat2], AND([_THISROW].[start_date] < “05/29/2022”, DAY([_THISROW].[start_date]) >= 23, WEEKDAY([_THISROW].[start_date])= 7, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[OTBsat1], AND([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) < 23, WEEKDAY([_THISROW].[start_date])= 7, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[OTBsat2], AND([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, WEEKDAY([_THISROW].[start_date])= 7, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[OTAsat1], AND([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) < 23, WEEKDAY([_THISROW].[start_date])= 7, [_THISROW].[manpower] = [manpower]))),

ANY(SELECT(oldACMSrates[OTAsat2], AND([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, WEEKDAY([_THISROW].[start_date])= 7, [_THISROW].[manpower] = [manpower]))),

)

IF(WEEKDAY([_THISROW].[start_date]) = 1,
LIST(
ANY(SELECT(oldACMSrates[OTBsun1], AND([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) < 23, [_THISROW].[manpower] = [manpower])))),

IF(WEEKDAY([_THISROW].[start_date]) = 7,
LIST(
ANY(SELECT(oldACMSrates[OTBsat1], AND([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) < 23, [_THISROW].[manpower] = [manpower])))),

IF(OR(WEEKDAY([_THISROW].[start_date])<>1, WEEKDAY([_THISROW].[start_date])<>7),
LIST(
ANY(SELECT(oldACMSrates[Bpeak1], AND([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) < 23, [_THISROW].[manpower] = [manpower])))),

IF(WEEKDAY([_THISROW].[start_date]) = 1,
LIST(
ANY(SELECT(oldACMSrates[OTBsun2], AND([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, [_THISROW].[manpower] = [manpower])))),

IF(WEEKDAY([_THISROW].[start_date]) = 7,
LIST(
ANY(SELECT(oldACMSrates[OTBsat2], AND([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, [_THISROW].[manpower] = [manpower])))),

IF(OR(WEEKDAY([_THISROW].[start_date])<>1, WEEKDAY([_THISROW].[start_date])<>7),
LIST(
ANY(SELECT(oldACMSrates[Bpeak2], AND([_THISROW].[start_date] >= “05/29/2022”, [_THISROW].[start_date] < “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, [_THISROW].[manpower] = [manpower])))),

IF(WEEKDAY([_THISROW].[start_date]) = 1,
LIST(
ANY(SELECT(oldACMSrates[OTAsun1], AND([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) < 23, [_THISROW].[manpower] = [manpower])))),

IF(WEEKDAY([_THISROW].[start_date]) = 7,
LIST(
ANY(SELECT(oldACMSrates[OTAsat1], AND([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) < 23, [_THISROW].[manpower] = [manpower])))),

IF(OR(WEEKDAY([_THISROW].[start_date])<>1, WEEKDAY([_THISROW].[start_date])<>7),
LIST(
ANY(SELECT(oldACMSrates[Apeak1], AND([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) < 23, [_THISROW].[manpower] = [manpower])))),

IF(WEEKDAY([_THISROW].[start_date]) = 1,
LIST(
ANY(SELECT(oldACMSrates[OTAsun2], AND([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, [_THISROW].[manpower] = [manpower])))),

IF(WEEKDAY([_THISROW].[start_date]) = 7,
LIST(
ANY(SELECT(oldACMSrates[OTAsat2], AND([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, [_THISROW].[manpower] = [manpower])))),

LIST(
ANY(SELECT(oldACMSrates[Apeak2], AND([_THISROW].[start_date] >= “10/01/2022”, DAY([_THISROW].[start_date]) >= 23, [_THISROW].[manpower] = [manpower]))))

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

but i just can get it to work.

2

here is the spreadsheet

https://docs.google.com/spreadsheets/d/e/2PACX-1vTMdu-44_RMzb2ueRv39V7DYvRE7ZwBMi7153dHIdMwLp83f-xOzqPwYwTJIMASLbpq_V6mCAjSzg_l/pubhtml

3

That expression is horrendous.

The answer is almost certainly to re-structure your data.

4 Likes