SOLVED

New Contributor

# Order Guide

Is there a way for me to have the dates populate in accordance with the future dates I will need? So, if I am printing out this OG for my staff on a Friday I want the OG to display dates in the yellow cells according to the pink days. So for example the first one says Monday for Wednesday. I want it to have Monday's date. The next cell says Wednesday for Friday. I want it to have Wednesdays date. and so on. Into the next week as well.

4 Replies
best response confirmed by SCJohnson3 (New Contributor)
Solution

# Re: Order Guide

Yes, definitely it can be done.

A couple questions for you--

1. What version of Excel are you working with. The formula I'm going to give you uses a very new function, LET, that requires the most recent version of Excel.
2. How comfortable are you in developing your own formula? I will give you, below, a formula I've written that will find the third Friday in any month of any year. If you like figuring things out for yourself, you could use the logic in it to create your own solution. If not, come back and I or somebody else here will help you take it to the next level. Here's that formula.

=LET(FstDa,DATE(D16,D17,1),FstDa+CHOOSE(WEEKDAY(FstDa),19,18,17,16,15,14,20))

For reference, D16 contains something like 2022  -- i.e., the year

D17 contains the number of a month --e.g., 1 for January

Given those two variables, this formula results in the date 1/21/22, the third Friday in Jan 2022

# Re: Order Guide

1. I am using version 2109 (build 14430.20342 Click-to Run)
2. I am not sure of anything-- I am a FAAFO person. If you tell me to copy and paste it into the cell and it works then I am happy. Otherwise, I am kinda' lost.
I am thankful you have replied. Thank you. I hope problem solving is a joy for you. I am trying to make my work faster so that I can spend time on other work. LOL. IDK I guess that is how most of us are?

# Re: Order Guide

OK let me give you a simple formula that will give next Monday, Wednesday and Friday's dates IF you run and print that spreadsheet on a Friday, as I think you said you do

For the next Monday: =TODAY()+3

For the next Wednesday: =TODAY()+5

For the next Friday: =TODAY()+7

And format the fields as dates.

I'm attaching a simple spreadsheet using those formulas. Today is a Friday, so you can see it at work. Just be aware that if you use it on a day other than Friday, all it's doing is adding 3, 5, or 6 to today's date.

# Re: Order Guide

Ah! Thank you so much! I can really use this. It will help two others in my organization as well.

Have a great 2022!