Net duration between 2 dates AND hours

Julie Barbeau

Hi,

I use this formula to calculate duration in minutes between two dates and time (see picture attached). Goal is to time delay of response between reception of email and answering email but to exclude hours people are not working.

Here is the formula to Start time : =IF(SUM(VALUE(LEFT(RIGHT([Date et heure du courriel du client]@row; LEN([Date et heure du courriel du client]@row) - 9); FIND(":"; RIGHT([Date et heure du courriel du client]@row; LEN([Date et heure du courriel du client]@row) - 9)) - 1))) = 12; 0; SUM(VALUE(LEFT(RIGHT([Date et heure du courriel du client]@row; LEN([Date et heure du courriel du client]@row) - 9); FIND(":"; RIGHT([Date et heure du courriel du client]@row; LEN([Date et heure du courriel du client]@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID([Date et heure du courriel du client]@row; FIND(":"; [Date et heure du courriel du client]@row) + 1; 2))) + IF(RIGHT([Date et heure du courriel du client]@row; 2) = "PM"; 720; 0) + 1

And the formula to End time : =IF(SUM(VALUE(LEFT(RIGHT([Date & heure confirmation commande ou soumission]@row; LEN([Date & heure confirmation commande ou soumission]@row) - 9); FIND(":"; RIGHT([Date & heure confirmation commande ou soumission]@row; LEN([Date & heure confirmation commande ou soumission]@row) - 9)) - 1))) = 12; 0; SUM(VALUE(LEFT(RIGHT([Date & heure confirmation commande ou soumission]@row; LEN([Date & heure confirmation commande ou soumission]@row) - 9); FIND(":"; RIGHT([Date & heure confirmation commande ou soumission]@row; LEN([Date & heure confirmation commande ou soumission]@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID([Date & heure confirmation commande ou soumission]@row; FIND(":"; [Date & heure confirmation commande ou soumission]@row) + 1; 2))) + IF(RIGHT([Date & heure confirmation commande ou soumission]@row; 2) = "PM"; 720; 0) + 1

Is there a way to remove from this duration the hours out of office so 63 hours for weekend (Friday 5PM to Monday 8AM) AND 14 hours from Monday to Thursday (6PM to 8AM next morning)

Thanks for your help :

Paul Newcome

My apologies for the delay. I must have missed the notifications that you had responded. I feel like we can greatly simplify your formulas for pulling the times. It looks like the formulas you are using are for calculating AM vs PM but you are using 24 hour times, and they also have quite a few extra steps in them.

To convert the times into minutes, you should be able to use this:

=(VALUE(MID([Date et heure du courriel du client]@row; 10; 2)) * 60) + VALUE(RIGHT([Date et heure du courriel du client]@row; 2))

Paul Newcome

It looks like you may have run into the annoying timezone issue. Can you post the exact formula you are currently using? We should be able to tweak it to basically say "if the hour is greater then or equal to 20 then subtract 1 day".

Paul Newcome

Make sure you updated the column names to the correct names that are in your sheet, and you may need to swap the commas out for semi-colons.

Paul Newcome

My suggestion would be to break this down a little bit further.

You will need one set of calculations that calculates how much time there was on the start date (1700 - Start Time).

You will need another set that calculates how much time on the end date (End Time - 800).

Next you would use something like the NETWORKDAYS function on the dates to determine how many working days there were, subtract 2 from that so as to not double count the start and finish dates, then multi[ply that result by 9 (9 working hours in each day).

=(NETWORKDAYS([Start date]@row, [End Date]@row) - 2) * 9

Adding all three of those together should give you the total amount of working hours elapsed from start to finish.

Julie Barbeau

Hi @Paul Newcome

Thank your for answering! I don't understand exactly what you mean. Adding 3 columns with the formula you suggest?

Paul Newcome

Yes. That would be my suggestion. Writing out each of the formulas in 3 separate columns and then adding those 3 columns up simply because some of the time calculation formulas can be a bear to work with/adjust/etc.

Julie Barbeau

Doesn't seems to work, see that example, if I sum the 3 column it gave me 580 minutes so almost 10 hours but should be almost 6 hours. Networdays results is zero

Julie Barbeau

Exact answer for row 1 should be 280 minutes

Paul Newcome

My post was working in hours. Sorry about that.

1700 = 1,020 minutes

800 (or 0800) = 480 minutes

Julie Barbeau

Great! Seems to work now :D Let see on monday if it still works after removing 2 days for the weekend :) Thanks a lot!

Julie Barbeau

HI @Paul Newcome

Seems to have problem when start date and end date are in the same day. See row 4 below. Délai en minutes is 591 but should be 0

Paul Newcome

I was afraid of that but hadn't had a chance to test that exact bit yet. When the start and end dates are the same, NETWORKDAYS will output a 1. So when we subtract 2 from that we get -1 which is where we are getting the -9 from. Try including that formula in an IF statement that says if both dates are the same then output zero.

=IF(DATEONLY([Start Date]@row) = DATEONLY([End Date]@row), 0, regular_networkdays_formula)

This also makes me realize that we are probably going to run into another issue. As I said before... My initial solution was working in hours and not minutes. That -9 is -9 hours. 9*60=540 minutes.

We may also need to address "same day" in the final calculation as well similar to how we addressed it in the NETWORKDAYS portion.

=IF(DATEONLY([Start Date]@row) = DATEONLY([End Date]@row), [End Time]@row - [Start Time]@row, adding_all_three_formula)

And thinking further on it... Addressing it in the final duration calculation means we do not need to adjust the NETWORKDAYS formula since we are skipping over it here.

Julie Barbeau

Thanks @Paul Newcome ,

Are NETWORKDAYS based on the business days identified in the admin center? Which means that holidays would not be counted if I add them in the next window?

Julie Barbeau

Hi @Paul Newcome

Sorry again, I still have a problem with holiday. By exemple, I set today (November 15th) as an holiday in sheet settings. So, for row number 1, Délai en minutes (duration) should be 710 minutes (170 minutes on thursday and 600 minutes on friday with 10 hours/day), but result is 946. In this case, what I understand is that I would need to remove End time - 800 column (176 minutes) cause the end date is a holiday. Do you know how I can do it?

Julie Barbeau

Hi @Paul Newcome ,

As discussed before in this post, I use these formula to calculate start time and end time.

Start time : =IF(SUM(VALUE(LEFT(RIGHT([Date et heure du courriel du client]@row; LEN([Date et heure du courriel du client]@row) - 9); FIND(":"; RIGHT([Date et heure du courriel du client]@row; LEN([Date et heure du courriel du client]@row) - 9)) - 1))) = 12; 0; SUM(VALUE(LEFT(RIGHT([Date et heure du courriel du client]@row; LEN([Date et heure du courriel du client]@row) - 9); FIND(":"; RIGHT([Date et heure du courriel du client]@row; LEN([Date et heure du courriel du client]@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID([Date et heure du courriel du client]@row; FIND(":"; [Date et heure du courriel du client]@row) + 1; 2))) + IF(RIGHT([Date et heure du courriel du client]@row; 2) = "PM"; 720; 0) + 1

End time : =IF(SUM(VALUE(LEFT(RIGHT([Date & heure confirmation commande ou soumission]@row; LEN([Date & heure confirmation commande ou soumission]@row) - 9); FIND(":"; RIGHT([Date & heure confirmation commande ou soumission]@row; LEN([Date & heure confirmation commande ou soumission]@row) - 9)) - 1))) = 12; 0; SUM(VALUE(LEFT(RIGHT([Date & heure confirmation commande ou soumission]@row; LEN([Date & heure confirmation commande ou soumission]@row) - 9); FIND(":"; RIGHT([Date & heure confirmation commande ou soumission]@row; LEN([Date & heure confirmation commande ou soumission]@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID([Date & heure confirmation commande ou soumission]@row; FIND(":"; [Date & heure confirmation commande ou soumission]@row) + 1; 2))) + IF(RIGHT([Date & heure confirmation commande ou soumission]@row; 2) = "PM"; 720; 0) + 1

How is it possible that two dates with similar start time and end time in reality have a big difference in start time and end time column? I don't understand :(

Julie Barbeau

@Paul Newcome following my last question our working hours are 10 hours by day, i don’t know if it should have impact on those 2 formula?

Paul Newcome

My apologies for the delay. I must have missed the notifications that you had responded. I feel like we can greatly simplify your formulas for pulling the times. It looks like the formulas you are using are for calculating AM vs PM but you are using 24 hour times, and they also have quite a few extra steps in them.

To convert the times into minutes, you should be able to use this:

=(VALUE(MID([Date et heure du courriel du client]@row; 10; 2)) * 60) + VALUE(RIGHT([Date et heure du courriel du client]@row; 2))

Julie Barbeau

Works well now! Thank you so much for your help :)