Forum Discussion
dates
need help creating a formula for the following situation:
return a value from a cell if the date is the 1st saturday of the month, or
a value from a different cell if date is 2nd saturday of the month, or
similarly for 3rd, 4th, or 5th Saturdays of the month
Steve-
Please review the attached worksheet I have added some formulas and notes to the FlowSheet and LeadSch. Unfortunately, the way you have set up LeadSch is prevents an Index Match formula from working. However, I have added a few different formulas that should help get you accomplish your task.
If you don't want some of the values like 1,2,3,4,5 to show in the worksheet you can format them as white font. This way the user doesn't see them....
10 Replies
I have a similar idea:
B4 calculate the first day of the month by using "Date(B1,B2,1)"
B5 find the weekday of the first day (Weekday(B4))
Then, for each number ranging from 1 to 5, I can find the date for saturday
Date(B1,B2,8-B$5), Date(B1,B2, 8-B$5+7), .... etcHowever, I do not want to have a long formula, so that I check wrong month in column C.
Hope that it is helpful.
Try using a formula like the below with some helper cells. It will help you to get to the end result. I'm sure you can incorporate it all into one formula if need be. May get lengthy though. Please see example .xlsx file for reference.
Hi Mike. I wasn't clear in my previous post. Very sorry. Please see attached. want to create a flow sheet which fills in 'completed by' cell based on which date the job was done. eg job 1 on date 1 was done by someone, job 1 on date 5 was done by someone else, etc. only want to enter date at top to have cells filled in as appropriate. Can you help?
Do you require a result like the following (See attached .xlsx file)?
