Nested IFs in query editor

[Anonymous]

Hi All,

Need your help in writing below formula from excel in custom column:

=IF(A2="AMS",IF(TIME(HOUR(B2),MINUTE(B2),SECOND(B2))>10:30:00 PM,DATE(YEAR(B2),MONTH(B2),DAY(B2))+1+11:30:00 AM+4:00:00,B2+4:00:00),IF(TIME(HOUR(B2),MINUTE(B2),SECOND(B2))>7:30:00 PM,DATE(YEAR(B2),MONTH(B2),DAY(B2))+1+11:30:00 AM+4:00:00,B2+4:00:00))

(Time highlighted in Orange is actual a decimal number in excel, However here I have pasted for ease)

Region	Start Date/Time	Closed Time	Target Date/Time	SL
EMEA	7/19/2017 13:04	7/19/2017 21:04	7/19/2017 17:04	Not-Met
AMS	7/19/2017 23:04	7/20/2017 14:04	7/20/2017 15:30	Met
APJ	7/19/2017 21:04	7/20/2017 18:04	7/20/2017 15:30	Not-Met
AMS	7/19/2017 13:04	7/19/2017 16:04	7/19/2017 17:04	Met

Thanks for your help.

Regards

Abhijit

[v-piga-msft · Accepted solution]

Hi  @Anonymous,

It seems that you are looking for how to add the conditional column.

You could try the following way which is much easier, you don't need to write the expression.

Best Regards,

Cherry

[v-piga-msft · Accepted solution]

Hi  @Anonymous,

It seems that you are looking for how to add the conditional column.

You could try the following way which is much easier, you don't need to write the expression.

Best Regards,

Cherry

[Anonymous · Accepted solution]

Hi @v-piga-msft,

Thank you for your suppoert. I did a work around and then break the query in 2 parts and then applied conditional formatting.

it worked for me.

Thanks for your help.