Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have been trying to figure out a way to calculate the out of a column depending on the values of other columns.
From the table below I would like to create an expression to say:
case if Level = 'AOG' and DateTime is between 18:00 and 08:00 (following morning) then 'AOG OO'
or if Level = 'AOG' and DateTime is on a Weekend then 'AOG OO' Else Level
Customer | Level | DateTime |
---|---|---|
A | AOG | 2018-05-01 09:08:06 |
B | Routine | 2018-05-03 13:23:04 |
B | Routine | 2018-05-07 19:11:07 |
A | AOG | 2018-05-11 23:30:02 |
OUTPUT REQUIRED:
Customer | OriginalLevel | NewLevel | DateTime |
---|---|---|---|
A | AOG | AOG | 2018-05-01 09:08:06 |
B | Routine | Routine | 2018-05-03 13:23:04 |
B | Routine | Routine | 2018-05-07 19:11:07 |
A | AOG | AOG OO | 2018-05-11 23:30:02 |
This calculation is being done to work out any levels that fall into out of hours (out of normal working hours).
Thank you in advance for your assistance.
Try this,
IF(Level = 'AOG' and (hour(DateTime)>18 and hour(DateTime)< 8 Or (weekday(DateTime)='Sat' or weekday(DateTime)='Sun'), 'AOG OO' , Level )
Thanks,
Hi Sasi,
This now works for weekends but still does not work for the time period.
Thanks all, I managed to get it working by doing the following:
=IF(PriorityLevel = 'AOG' and (hour(OutTimestamp)>18 and hour(OutTimestamp)<24) Or (AMPM = 'AM' and hour(OutTimestamp)<8) Or (weekday(OutTimestamp)='Sat' or weekday(OutTimestamp)='Sun'),'AOG OOH',PriorityLevel)
Please mark you response as correct answer, Then?