calculating the last WEDNESDAY in Jan
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 s...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendCan 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
mark.shnier@gmail.com
------------------------------
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.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
------------------------------
I could modify it to pick a date in the previous years or next year, I guess.
------------------------------
Melissa Freel
------------------------------
- MarkShnier__YouSo 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
mark.shnier@gmail.com
------------------------------- MarkShnier__YouI'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 Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------- Looking at the lengthy response, I think I need to better explain my use case. In the app that I am building, I have to do a simple point in time count of my records that meet certain criteria. So, on the last Wednesday of January (regardless of the year, it just needs to be the last Wednesday of January), I need to count all records in my app that are "active" on that date, whatever that date is. I don't want to manually figure out the last Wednesday of every January in the past and in the future. I want to return whatever that date is in a field and then create other fields that capture the appropriate records. Does this make more sense?
------------------------------
Melissa Freel
------------------------------