e-Commerce order lead-time calculation

filippo_87

New Member
Joined
Jan 21, 2018
Messages
3
Hi all,

I work for an e-retailer and we are currently building up our KPIs dashboard. One of these, is the order lead-time. I will need to know how long it takes (IN HOURS, NOT IN DAYS) for my OPS team to dispatch an order. This calculation is based on a 24 hours working schedule. I will need to exclude weekends from the calculation. Below the involved data in my hands. Anybody can help on this please?

Order received into the system onOrder dispatched on
27-11-2017 17:10:1729-11-2017 15:23:14

<colgroup><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You can find a detailed formula in the link below:

http://www.exceltactics.com/calculate-net-work-hours-using-networkdays/
 
Upvote 0
Thank you Flashbond.

I have tried your suggestion but get an error message: A value used in the formula is of the wrong data type.

Can you please help me understand where the issue with the formula is? Appreciate your help
 
Upvote 0
Thank you Flashbond.

I have tried your suggestion but get an error message: A value used in the formula is of the wrong data type.

Can you please help me understand where the issue with the formula is? Appreciate your help

All that I can say is your dates are in wrong format. Please check your date formats again. The formula works well on my computer.

Also check for A1 and B1 cell referances. Your values may be in different cells.
 
Last edited by a moderator:
Upvote 0
Flashbond's formula works for me.......but here's another way to get the answer as a time value, e.g. 46:12 for your example. With start time in A2 and end time in B2 use this formula in C2

=NETWORKDAYS(A2,B2)-1+MOD(B2,1)-MOD(A2,1)

custom format C2 as [h]:mm
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top