Completion time

[Pushkar92]

I have an excel with 4 column.

1. Request received date

2. Request received time

3. Completion date 

4. Completion time

For every urgent request completion time is 5 hours.

We will have to focus on those urgent request which we receive between 9am to 6pm.

So I have to calculate time taken to complete any  urgent request which we receive taking the four points mentioned above. 

Need help in understanding how that can be done.

[atcodedog05]

Hi @Pushkar92 

Can you provide sample data and expected output. We can take a look into it 🙂

[Pushkar92]

Hi,

Thank you for your response.

I have attached both input data and output file below.

[atcodedog05]

Hi @Pushkar92 

Here is how you can do it.

Formula:

DateTimeDiff(
[Request Completion Date]+" "+[Request Completion Time],
[Request Received Date]+" "+[Request Received Time],
"minutes")/60

Workflow:

Hope this helps : )

[atcodedog05]

Hi @Pushkar92 

I took a 2nd try to get the decimal in the minutes system.

Workflow:

Hope this helps : )

[Pushkar92]

Hi, Thank you for responding. For last two entry Request Received Date Request Received Time Request Completion Date Request Completion Time 7-Jul-21 5:40 PM 8-Jul-21 9:55 AM 7-Jul-21 5:55 PM 8-Jul-21 10:55 AM Request received on 7th but completed on 8th. As mentioned above urgent request received between 9am to 6pm will be worked on the date of receiving request. But if any request received 9am to 6pm but could not be completed the same day. Then time taken to complete will be time between Request Received Time and 6PM of the same day and the time taken the next day after 9 AM. For example Request Received Date Request Received Time Request Completion Date Request Completion Time Completion Time 7-Jul-21 17:40:00 PM 8-Jul-21 9:55 AM (17:40-18:00)+(9:00-9:55) 7-Jul-21 17:55:00 PM 8-Jul-21 10:55 AM (17:55-18:00)+(9:00-10:55)

[atcodedog05 · Accepted solution]

Hi @Pushkar92 

Got it only 9am - 6pm duration should be considered if the completion date is not the same then 6pm-9am(15hrs) duration should not be considered.

Workflow:

Hope this helps : )

[Pushkar92]

This is great. Thank you so much.

Please help me with formula used here.

[atcodedog05]

Hi @Pushkar92 

I have attached the updated workflow in the previous post. Attaching again for reference.

Guess you found it 🙂

[Pushkar92]

Hi,

I am sorry. I have having trouble with opening .yxmd file.

Can u please paste the formula manually in here.