Forum Discussion
Comparing the dates from a CALENDAR table with the dates of another table
Hi Anonymous ,
after a fierce battle I managed to solve the problem.
I created a Holiday Calendar (only with holidays) and a normal one. Then I excluded both holidays and weekends and with RANKX I got the "Next business day".
From my database I have a column which gives me the response time.
Example: I miss a call from my client at 15:00 and I call him at 17:30 (response time). My response time must be max. 4 hours after he has tried to contact me. My workday starts at 08:00 and ends at 18:00. So I have time to contact him until 09:00 on the "Next business day" (from 15:00 till 18:00 are 3h. and we have 1h. transfered to the "Next business day" which starts at 08:00. )
Solution: I created a column which only has 18:00:00 as a value and then I substracted it from the initial customer call time. If it is > than 4h I must call him on the same day and if it is < than 4h I can call him on the " Next business day" + the remaining time from the previous day.
Finally with a simple IF I created a column which shows the data in both cases.
Thank you! Regards! 😊
Hi specbk,
Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi Anonymous ,
after a fierce battle I managed to solve the problem.
I created a Holiday Calendar (only with holidays) and a normal one. Then I excluded both holidays and weekends and with RANKX I got the "Next business day".
From my database I have a column which gives me the response time.
Example: I miss a call from my client at 15:00 and I call him at 17:30 (response time). My response time must be max. 4 hours after he has tried to contact me. My workday starts at 08:00 and ends at 18:00. So I have time to contact him until 09:00 on the "Next business day" (from 15:00 till 18:00 are 3h. and we have 1h. transfered to the "Next business day" which starts at 08:00. )
Solution: I created a column which only has 18:00:00 as a value and then I substracted it from the initial customer call time. If it is > than 4h I must call him on the same day and if it is < than 4h I can call him on the " Next business day" + the remaining time from the previous day.
Finally with a simple IF I created a column which shows the data in both cases.
Thank you! Regards! 😊
Hi specbk,
Glad to hear you find out the solution and share it here. I think they should help others who face a similar scenario.
Regards,
Xiaoxin Sheng