Forum Discussion

Steve Haines's avatar
Steve Haines
Copper Contributor
May 23, 2018
Solved

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 mo...
excel
Formulas and Functions
  • Matt Mickle's avatar
    Matt Mickle
    May 28, 2018

    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....

     

     

     

     

Man Fai Chan's avatar
Man Fai Chan
Iron Contributor
May 25, 2018

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), .... etc

 

However, I do not want to have a long formula, so that I check wrong month in column C. 

Hope that it is helpful.