Terri,
The Quick Base function ToDate() takes text and produces a date. Then you can use the resulting date in your report filters. In order to have your report dynamically give you the Report Start Date and Report End Date for your filters you have to progressively march through Quick Base functions to get the final answers you need for the two dates in your filters.
The functions you will use are
Today() this gives you a date result of what ever today's date actually is.
AdjustMonth() gives you a date that is a certain number of months behind or ahead of the argument in the function.
Month() will give you the number of the month in the date you use on it.
Year() will give you the number of the year in the date you use on it.
ToText() will convert numbers into text
ToDate() will create the date you need, but you have to feed it a text argument.
So marching through the logic
Today() will give you, 1 Feb, 2021 because as I am writing this, the date is 1 February, 2021
AdjustMonth(Today(),-2) will give you 1 December 2020 which is two months less than today.
Month(AdjustMonth(Today(),-2) ) will give you 12 because December is the 12th month of the year
ToText(Month(AdjustMonth(Today(),-2) )) will give you 12 as a text value and not a number
Year(AdjustMonth(Today(),-2) ) will give you 2020 which is the year from two months back
ToText(Year(AdjustMonth(Today(),-2) )) will give you 2020 as a text value
Now you know all the components of your Report Start Date from two months ago. You just need to turn those components into a real date so that Quick Base can use it against your [Start Date]
You put together text strings in Quick Base using the ampersand to concatenate them. That looks like this
"21" & "/" & "12" & "/" & "2020"
will give you the text 21/12/2020. It looks like a date to you but not to the computer.
You have to finish by using ToDate("21/12/2020")
However your report would always create think it the 21st of December, 2020 and you would have to change it every month. Instead we use the functions above to produce the 21st of two months ago.
Create a second variable called Report End Date but change the formula to build the end date for the 5th one month ago
Your AdjustMonth() will use a -1 instead of a -2
Again if today is 1 Feb, 2021 you will get a result of 1 Jan 2021
Your value for Day is going to be a 5 not the 21.
Now you can use these in your report filters.
Repeat in your second report to create a different Report Start Date and Report End Date for the 6th to the 20th.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------