backdating issue

[chunter]

We receiving orders for our products with an arrival date. I need to back up the date a certain number of transit days (depending on customer location) and then back up to the day of the week that customer truck leaves.

For example, Say we get an order for a part due to arrive at the customer on 7/19/11. Say this customer should have 3 days transit time and the truck only leaves on Mondays. I actually have to ship this on 7/11/11.

I've got lots of customers that will each have their own rules and have to compare them against a huge list of products. I'm planning to use a macro to loop through each row and calculate the new date. This'll save use a few hours per week in manpower plus help ensure our shipping is on time.

Any suggestions?

I've written lots of macros before but never have I dealt with dates.

 

[shawnhet]

Based on my experience with this sort of thing, I would recommend using formulas instead of macros. The reason I say that is that for most companies, these sorts of things can change fairly often.

Using formulas, you could have one sheet on your workbook devoted to transit times from your customers, one sheet with time it takes different to leave the warehouse and one for when trucks are sent out. Then, for a simple example, you could use VLOOKUPS to pull the appropriate information for each order, then you could take the max of all the picking times for the order + add the max transit time to the customer together with an if statement to state what day of the week the appropriate truck would leave to get a total transit time which you could then use to calculate the appropriate date to ship the items.

Obviously, the specific statements will depend on what your rules look like, but this sort of framework is pretty flexible.

Cheers,

 

[chunter]

it looks like I may have gotten it to work. I used the weekday of the part to calculate how many days into this week it was. Added in how many days back into the previous week it had to ship. Subtracted this total from the received date and reentered it into a cell. Then I loop through each row. The only lengthy part of the program is supplying the information for each customer.

Now on to the next problem with this report.

 

[shawnhet]

Congratulations!