How to custom date/time column to fit working schedule so I can do calculation correctly

[njxfoster]

Hi Guys

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

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

[VahidDM]

Hi @njxfoster 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[njxfoster]

Basiclly I want to transform every record thats passed 7:00:00PM into 10AM the next day

For example first row is 10/13/2021 8:56:00PM,  the record passed 7:00:00PM so after transformation it should be 10/14/2021 10:00:00AM

The fifth record is 2:42:00PM. Its before 7 PM, so it should stay the same

Thank you for your reply!!

[VahidDM · Accepted solution]

Hi @njxfoster 

Try this code to add a new column to your table:

New Column = 
Var _PD = time(19,00,00)
Var _SND = time (10,0,0)
Var _Date = DATEVALUE('Table'[Created Date/Time])
Var _Time = TIMEVALUE('Table'[Created Date/Time])
return
if(_Time>_PD,(_Date+1)+_SND,'Table'[Created Date/Time])

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/