HI can anyone give me a sample date formula that easily calculates the last WEDNESDAY in January. I need this field to work "backwards" and "forwards" if possible. Meaning, when used, I am able to say what is the last WED in Jan 19, 20, 21, etc. Any help is appreciated.Melissa------------------------------Melissa Freel------------------------------

Mark: Figured it out! There was a slight error in your math I bolded the corrections below and works like a dream now!Thanks Much!!! you saved me a ton of time.If( DayOfWeek(LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(0))=3, LastDayOfMonth(Date(Year($DateField),$MonthNum,1)), DayOfWeek(LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(1))=3, LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(1), DayOfWeek(LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(2))=3, LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(2), DayOfWeek(LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(3))=3, LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(3), DayOfWeek(LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(4))=3, LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(4), DayOfWeek(LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(5))=3, LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(5), DayOfWeek(LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(6))=3, LastDayOfMonth(Date(Year($DateField),$MonthNum,1))-Days(6)) ------------------------------Melissa Freel------------------------------

Can you tell us how to know which year you are getting to? Do you have a record that knows the a date which would then let is know the year if that date field?------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Yes, I made a ReportDate=Today()I could modify it to pick a date in the previous years or next year, I guess.------------------------------Melissa Freel------------------------------

So do you want the last Wednesday in January of the current year?------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

I'm still now sure which year you are referring to but this formula does give the the date of the last Wesdesday in January for the date in the first line.var date DateField = Today();If(DayOfWeek(LastDayOfMonth(Date(Year($DateField),1,1))-Days(0))=3,LastDayOfMonth(Date(Year($DateField),1,1)),DayOfWeek(LastDayOfMonth(Date(Year($DateField),1,1))-Days(1))=3,LastDayOfMonth(Date(Year($DateField),1,1))-Days(2),DayOfWeek(LastDayOfMonth(Date(Year($DateField),1,1))-Days(2))=3,LastDayOfMonth(Date(Year($DateField),1,1))-Days(2),DayOfWeek(LastDayOfMonth(Date(Year($DateField),1,1))-Days(3))=3,LastDayOfMonth(Date(Year($DateField),1,1))-Days(1),DayOfWeek(LastDayOfMonth(Date(Year($DateField),1,1))-Days(4))=3,LastDayOfMonth(Date(Year($DateField),1,1))-Days(4),DayOfWeek(LastDayOfMonth(Date(Year($DateField),1,1))-Days(5))=3,LastDayOfMonth(Date(Year($DateField),1,1))-Days(5),DayOfWeek(LastDayOfMonth(Date(Year($DateField),1,1))-Days(6))=3,LastDayOfMonth(Date(Year($DateField),1,1))-Days(7))------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

calculating the last WEDNESDAY in Jan

13 Replies

MarkShnier__You
Qrew Legend
6 years ago
Can you tell us how to know which year you are getting to? Do you have a record that knows the a date which would then let is know the year if that date field?

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
[email protected]
------------------------------
- MelissaFreel1
  Qrew Cadet
  6 years ago
  Yes, I made a ReportDate=Today()
  I could modify it to pick a date in the previous years or next year, I guess.
  
  ------------------------------
  Melissa Freel
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    6 years ago
    So do you want the last Wednesday in January of the current year?
    
    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    [email protected]
    ------------------------------

Forum Discussion

calculating the last WEDNESDAY in Jan

13 Replies

Related Content

Qrew Tip: Calculations per Record

Snapshot calculations

Formula to calculate hours

Calculating 1.5 pay

Calculation/Performance Limits Inquiry