Date Formula for Reporting

Question

I am trying to generate 2 monthly reports: One comes out on the first of the month and one comes out on the 16th of the month. I have a Start Date field. I'm trying to figure out how to filter the reports so the jobs with Start Dates fall on these reports:

First of the month: Start Date on or after the 21st of 2 months previous and Start Date on or before the 5th of the previous month (i.e for the February 1 report, Start Date would be December 21 - January 5

16th of the month: Start Date on or after the 6th of the previous month and Start Date on or before the 20th of the previous month (i.e. for the February 20 report, Start Date would be January 6 - January 20)

------------------------------
Terri
------------------------------

donlarson · Answer

Terri,If these two reports are the only place that you care about the Numerical Day in the field [Start], then a simple solution is to use Report Formulas.Here is a screen shot of it being set up.&nbsp; &nbsp;This assumes that [Start] is Date field and NOT a Date/Time field.&nbsp; &nbsp;If this assumption is false then inside the formula you first have to convert [Start]&nbsp; to a Date and then use Day() on the result.------------------------------Don LarsonPaasporterWestlake OH------------------------------

terribruce · Answer

Thank you, Don. I've never worked with Report formulas before so I've learned something new. I can't figure out how to make it work for the report I need on the 1st of the month because it has to be >20 two months ago but <5 last month. Any suggestions?

------------------------------
Terri
------------------------------

donlarson · Answer

Terri,I read your date ranges incorrectly the first time.&nbsp; I am going to assume that you will have two separate reports on the 1st or 16th, export the data and then not use those reports again.Build custom dates based upon Today() that will be the correct dates for your report on that day using the ToDate() function.&nbsp; ToDate("1/30/00") will give you January 30th of 2000 as a date field that you can report on.&nbsp; &nbsp;You just need to build the month, day and year pieces formulaically.&nbsp;

Take Today() and move it to the calendar month you want using AdjustMonth().
Then strip out the value of the Month() or value of the Year()
Change them to Text values using ToText() which is what ToDate() requires as the argument of the function
String them together to build the date that you want using quotes, "",&nbsp; and ampersands , &amp;, to get the text string you need.

Here is an exampleThat should give you the 21st day of the month from two months back.&nbsp; Edit and repeat this for your Report Stop Date.------------------------------Don LarsonPaasporterWestlake OH------------------------------

terribruce · Answer

Hi Don,I'm sorry. I'm really confused.&nbsp; I don't understand why I need to make it a text? And then how do I use that formula to filter my report? I can't figure out how to compare my job start dates to the date generated in that formula so the correct jobs show up on the monthly reports.------------------------------Terri------------------------------

donlarson · Answer

Terri,The Quick Base function ToDate()&nbsp; takes text and produces a date.&nbsp; &nbsp;Then you can use the resulting date in your report filters.&nbsp; 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 areToday()&nbsp; &nbsp;this gives you a date result of what ever today's date actually is.&nbsp;&nbsp;AdjustMonth()&nbsp; &nbsp; &nbsp;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.&nbsp;&nbsp;Year()&nbsp; will give you the number of the year in the date you use on it.ToText() will convert numbers into textToDate()&nbsp; will create the date you need, but you have to feed it a text argument.So marching through the logicToday() will give you, 1 Feb, 2021&nbsp; because as I am writing this, the date is 1 February, 2021AdjustMonth(Today(),-2)&nbsp; &nbsp;will give you 1 December 2020 which is two months less than today.Month(AdjustMonth(Today(),-2) )&nbsp; will give you 12 because December is the 12th month of the yearToText(Month(AdjustMonth(Today(),-2) ))&nbsp; will give you 12 as a text value and not a numberYear(AdjustMonth(Today(),-2) ) will give you 2020 which is the year from two months backToText(Year(AdjustMonth(Today(),-2) ))&nbsp; will give you 2020 as a text valueNow you know all the components of your Report Start Date from two months ago.&nbsp; 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.&nbsp; That looks like this&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "21" &amp; "/" &amp; "12" &amp; "/" &amp; "2020"&nbsp; will give you the text&nbsp; 21/12/2020.&nbsp; 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.&nbsp; &nbsp;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 agoYour AdjustMonth() will use a -1 instead of a -2&nbsp;Again if today is 1 Feb, 2021&nbsp; you will get a result of 1 Jan 2021Your 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 LarsonPaasporterWestlake OH------------------------------

Forum Discussion

Date Formula for Reporting

Related Content

Report Formulas

Color Code Date Field based on Formula

Formula Date Time Will not Accept Null Condition

Merging 2 Fields into a Formula-Text Field for Reporting Purposes

Formula Query for null dates