Forum Discussion
Nested IFs in query editor
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
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
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.
2 Replies
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
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.