How to custom date/time column to fit working sche...

njxfoster · ‎01-19-2022

Hi Guys

How can I design a column which transform every time that's after 7pm to Next days 10am? Maybe with a TRANSFORMIF?

I need to create a column about lead reaction time. Which is (Attempt Time - Lead Created Time). However, some Lead Created Time is created after work time, So it can't be use for this calculation. (For example, If a lead is created at 01/01/2021 10:00pm, if a agent response to it at 01/02/2021 10:20am, the response duration will be 12 hours 20 mins. Which is not right.

In order to get the right result I have to set every time thats passed 7pm to 10 am the day after

Your help will be very appraciated!

Thanks in advance

Anonymous · ‎01-26-2022

Hi @njxfoster ,

Can you provide dummy data and expected results?

For now, I suggest that you use the IF function to determine the date/time and make different calculations.

Do you have a column for creation time in your data?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

KNP · ‎01-19-2022

You could create a column that produced a 1 if inside business hours and a 2 if it is outside of business hours and then increment a date, today at 10am, by the new column.

Paste in some data if you would like some examples built for you.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.