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.

Workflow

Accepted answers

atcodedog05

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 : )

duration_v2.yxmd

All comments

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.

Input Data.xlsx

Output.xlsx

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 : )

duration.yxmd

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)

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703