Creating a formula to lookup a cell and return date relevant to period

[Marcel Coetzee]

I'm trying to create a formula that will return a specific date relevant to a day and period. Please refer to excel attachment. I will try and explain what I need.

My spreadsheet is set up to manage our month end tasks. WD-1 will correctly calculate the date as the last day (as in cell E2) less 1 day and WD0 will correctly calculate to be equal to cell E2.

My problem starts with WD1...now it seem simple and one would think that a Vlookup will solve the issue, but my problem is that we only work weekdays and therefore need to exclude weekends. I'm trying to write a formula that will calculate the correct date for each working day as the period changes (in cell F2). On sheet "WD breakdown" I have listed the working days plus the date applicable to the specific day for each period.

In summary what I want my formula to do is pick up the correct working day when I change the period in F2

[ajryan88]

Hi,

Take a look at how I have changed your formula in cell B7 on the "EOM Schedule" worksheet - it now refers to a named range. This named range will find the specific range to lookup on the "WD Breakdown" worksheet, based entirely on what period you enter in cell F2 on the "EOM Schedule" worksheet.

I wasn't sure if you wanted this formula applied for WD2, WD3, etc... or not, so I didn't, but I'm sure that now that you have my formula to work with you should be able to make it work for all working days.

Let me know how you go with this

[Marcel Coetzee]

That worked perfectly! Thanks so much!

[ajryan88]

No problems! Happy to help

Please don't forget to mark this thread as solved and click on the star beneath my post to add some reputation.

Thanks