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