How To Estimate Business Shipping Days

[JonBiegen]

Hello All!

My name is Jon, I'm new here and to coding in Excel.

I work for a retailer that has several shipping options available.

Standard - 3-6 Business days
Two Day - Two Business days
Next Day - 1 Business day
Saturday Delivery - Next Saturday (If placed before 2:00 PM CST the Friday before)


These timeframes are based on if the order is placed before 2:00 PM CST on the next business day.

I would love to make a sheet that will list based on what date it is, an estimated day an order could arrive. I had tried messing around with this with the NETWORKDAYS function, but the trouble is I would also need to calculate if the current day is a business day, and if the order is placed before or after 2:00 PM CST that would add a day.

This is a fun one.
Does anyone have any ideas on how I can get this done?

Thanks!

 

[NARAYANK991]

Hi Jon ,

Can you upload a workbook with sample data ?

Narayan

 

[JonBiegen]

I'm trying to upload as an .xls and am receiving an error message.

So heres a dropbox link: https://www.dropbox.com/s/rmj1l1otwnkjzpw/Shipping timeframes Chandoo.xls

I also included more info about timeframes that will effect this, It may need a refresh button to refresh the time, but if it could update automatically with a macro that would be great too. thanks for offering help!

 

[NARAYANK991]

Hi Jon ,

Thanks. Please wait for a day or so. I am uploading your file , as it is , so that others may also be able to download it from this forum , and help out.

Narayan

 

[JonBiegen]

That's great news! Thank you so much, I think this will be a fun one to figure out. I appreciate it.