Forum Discussion

Qrew Cadet

4 years ago

Date Formula for Reporting

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

Formulas and functions

reports and charts

Qrew Commander

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. This assumes that [Start] is Date field and NOT a Date/Time field. If this assumption is false then inside the formula you first have to convert [Start] to a Date and then use Day() on the result.

------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------

Qrew Cadet

4 years ago

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
Qrew Commander
4 years ago
Terri,

I read your date ranges incorrectly the first time. 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. ToDate("1/30/00") will give you January 30th of 2000 as a date field that you can report on. You just need to build the month, day and year pieces formulaically.

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, "", and ampersands , &, to get the text string you need.

Here is an example

That should give you the 21st day of the month from two months back. Edit and repeat this for your Report Stop Date.

------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
- TerriBruce
  Qrew Cadet
  4 years ago
  Hi Don,
  
  I'm sorry. I'm really confused. 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
    Qrew Commander
    4 years ago
    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
    ------------------------------