Using OFFSET to change receipt day to order day but to skip over weekends

Hi all,

I am new to this forum... hoping someone might be able to help me out with something I am trying to put together!

In the attached file, under the Paste Here tab, I have a data extract dump showing stock on the day of RECEIPT by distribution centre by product. Unfortunately, I need this to reflect the actual day of order.

To do this I have put the lead times for each DC in the Lead Times tab (in working days, not counting Sat & Sun) and have added a Result tab where I have been using the OFFSET formula with a VLOOKUP nested in it to move the data back the relevant number of lead days to give me the actual order day which is what I really need.

The part I can't work out is how to get it to skip over the weekends and not count them as a lead day if indeed this is the case (sometimes affected by weekend, sometimes not). Normally I could just add 2 days to each DC's lead time under the Lead Time tab but this won't work as some DC's I order Monday for Thursday (3 working lead days) and then the same DC again Wednesday for Monday (3 working lead days again, but 5 in total).

Is it possible to add some IF statements in there somehow to recognize if the data is being moved across a weekend date then to ignore and not count that?

Hope that makes sense, any help I would really appreciate it. Apologies for the ZIP file it was slightly over 1 MB!

Cheers,
Shane