Networkdays

  • Thread starter Thread starter Wilfred
  • Start date Start date
W

Wilfred

Is there a way to use "Netwokdays" to produce a date?

I already use Networkdays in a spreadsheet to calculate the number of
business days between order date and delivery date. I would like to add a
cell with a formula that would return a date for a given zipcode.

For example, if the zipcode is 20001, and the delivery allowance for that
zipcode is 5 workdays, calculate the date that is 5 workdays prior to today.
 
First, I'd think you'd need to have a table that has the zip codes and the
delivery allowance. I'd do a lookup off of that table in the original
table and subtract the delivery allowance from today's date - today()
 
I neglected to account for the weekends. You'll need to do that somehow in
your calculation.
 
You can use the WORKDAY function. For example, with zip codes and days
in a table named ZipTable, order date in B2, and Zip Code in C2:

=WORKDAY(B2,VLOOKUP(C2,ZipTable,2,0))
 
You would need to creat a table with zipcodes and their business days
delivery , i.e.

20001 5
84001 7


etc

now you can use VLOOKUP to get the days

=VLOOKUP(A2,Sheet2!A1:B5000,2,0)

where A2 would hold the zip you want to check and Sheet2 holds the
zip/delivery table (adapt to fit accordingly)

then use the WORKDAY function as follows

=WORKDAY(TODAY(),VLOOKUP(A2,Sheet2!A1:B5000,2,0),Holidays)

which will take for instance the 5 days from your example, add it to today's
date and return a future date when the item is supposed to be delivered
 
Back
Top