Dec 17 2021 09:04 AM
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.
Dec 17 2021 09:34 AM
Solution
Yes, definitely it can be done.
A couple questions for you--
=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
Dec 17 2021 09:41 AM - edited Dec 17 2021 09:42 AM
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?
Dec 17 2021 10:05 AM
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.
Dec 17 2021 11:01 AM
Ah! Thank you so much! I can really use this. It will help two others in my organization as well.
Have a great 2022!
Dec 17 2021 09:34 AM
Solution
Yes, definitely it can be done.
A couple questions for you--
=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