RE: Saved Search Formula Not Working
Hello! I’m working on cleaning up and updating a saved search for resource forecasting that a predecessor set up. It is set up to look at an event start and end time for an employee and if they have an event at that time to show “NA”. However for the 8:00-8:15AM (converted to decimal) time bucket and a couple other time buckets, the formula doesn’t work, (shows “A” when should be “NA”) but works for the majority of them. I’ve checked the event set up and nothing sticks out as being off as well as adjusting the number of decimal places.
CASE
WHEN {starttime}- TRUNC({starttime})=0.333333333333333 OR (0.333333333333333>{starttime}- TRUNC({starttime}) AND 0.333333333333333<{endtime}-TRUNC({endtime}))
THEN ‘NA’
ELSE ‘A’
END
Thanks for your help in advance!
Hi,
I get the feeling there may be an easier overall solution but working with what you have, I’d convert everything to minutes in order to get rid of those decimals:
CASE WHEN 24*60*({starttime}-TRUNC({starttime}))=480 OR (480>24*60*({starttime}-TRUNC({starttime})) AND 480<24*60*({endtime}-TRUNC({endtime}))) THEN 'NA' ELSE 'A' END
If that doesn’t work then theres’ something more fundamentally wrong with the logic.
Let me know if you need any further help.
Thanks,
Chris
