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
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__You
Qrew Legend
Qrew LegendSo 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
------------------------------
------------------------------
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
------------------------------- MarkShnier__YouSorry, but I still am not understanding. What if a record was active over a span of two years? Does it get counted twice? Do you only need to know the active record count during January so you can do your reporting or do you need to be able to look back at how many records were active last January and the one before that?
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------