Determining Prior Dates on Excel

[Buttertoasting]

Good day!

I would like to ask for help and guidance about a complicated spreadsheet. I just accepted a new position in a retail company and one of the tasks is to determine a date prior to what they call a "Reset Date" There are so many conditions and since this is going to be one of my recurring task, I would really appreciate if anybody can help me out to make it a little easy than doing it manually. So here it goes!

Basically, what I need is to get the prior date that matches the conditions. Column M is where the results should be. On the attached file, you can see example on Row 4. Reset Date (Column E) is when the delivery should be, Week 1&3 and 2&4 means the week of the month (Week 1 would be Feb 3-7, Week 2 is Feb 10-14 etc..) It has alternating schedule. I need to get the date that matches the day in column L prior the reset date. On row 4, the early delivery would be 2/7/20 because this is the nearest possible date we can deliver according to the week (Column J) and day (Column L). I know it is very complicated and I am willing to change formats if I have to. I need help, please!

Thank you! Hoping to hear from you soon!

[Rick Rothstein]

Does Week 1 start on the first Monday of the month?

[Buttertoasting]

Yes sir. Always Monday.

[Rick Rothstein]

Sorry, but I have a couple of more questions...

1) What if the day in the day in Column L is the same day as the reset date... do we use the reset date or the week before that?

2) Your write up say Column J will have 1&3 or 2&4, but, looking at your example sheet, I see the word "All" for the last few records... what does "All" mean in terms of calculating things?

[Buttertoasting]

Sir Rick,

Thank you for trying to help me. I understand that this is somewhat complicated spreadsheet they were using and they have always done it manually.

1. I need to have the prior possible date, so it should be a week before it.
2. All means it could be in all weeks. The routes for the stores that has the designated week is designed due to their location.

Thank you so much!

[Rick Rothstein]

1. I need to have the prior possible date, so it should be a week before it.

Actually, in thinking about it, it probably would not be the week before unless the Column J value was ALL... if the Column J value was 1&3, then the prior date would have to occur in week 3 of the previous month... is that correct?

This brings up another question. What if day arrangement for the month was such that the date prior to the Reset Date calculated to be in the days after the end of the 4th week (I'm thinking of something like the first of January being the first day of the month and the prior calculated date occurred on, say, January 30th)... what date should be used, the calculated date or the date the physically occurs in the 4th week?

Also, you posted your question in the Formula & Functions sub-forum, but I cannot envision a (sensible) formula that could account for all the variations that seem to be part of your requirements, so would a VBA macro solution be acceptable?

[Buttertoasting]

Sir Rick,

Good morning! Yes, anything that would make this spreadsheet work will be greatly appreciated. Thank you so much.

[Rick Rothstein]

Sir Rick,

Good morning! Yes, anything that would make this spreadsheet work will be greatly appreciated. Thank you so much.

Well, you answered my last question but ignored the two above it... I need answers to them so I know how to proceed with a solution for you.

[Buttertoasting]

Hi Sir Rick!

I apologize, I meant to answer it at the time, but, something came up. I really appreciate your time in helping me with this.

For the first one, Yes sir, that is correct.
For the 2nd one, it should bring the calculated date as well. If it is the nearest possible date prior to reset date and corresponds with the day indicated on Column L.

[Rick Rothstein]

2. All means it could be in all weeks. The routes for the stores that has the designated week is designed due to their location.

I need a clarification on the above answer of yours. If "All" is specified in Column J, is the calculated EarlyDelivery date restricted to the first 4 weeks of the month (in other words, is "All" just 1&3 combined with 2&4) or could it be any date in the month even if that date is after the 4th week?

[Buttertoasting]

It could be any day of the month as long as its prior the reset date, as long as it corresponds to the day assigned on column L.

[Rick Rothstein]

Okay, if I have understood all of your requirements correctly, then this macro should fill in Column M with the correct Early Delivery dates (you will need to test it on a copy of known correct file to see if the dates match up)...

Please Login or Register  to view this content.

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (EarlyDelivery) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

[Buttertoasting]

Wow! This is awesome! Thank you so much for your help and I can't wait to test it!

[Rick Rothstein]

@Buttertoasting,

You are quite welcome. Please let me know if the code works or, of course, if it did not work as I am interest how well I did in interpreting your requirements.

[Buttertoasting]

Sir Rick!

This is perfect! This is what I really need! Thank you so much for your time and effort, I truly appreciate it!