Forum Discussion

TrishWehrle's avatar
TrishWehrle
Qrew Cadet
6 years ago

Field to capture months a project was worked

We have projects that span multiple months. I have a field for actual [start date] and [end date]. However we could start in January and end in May. People will as me to pull reports for all projects that were in work in February. I'm thinking I need a field that will select the months that are between start date and end date so I can filter off that new field looking for projects with (in this case) February selected. Is that possible?
Another thought I had was to have an automation on a schedule that would run the first of the month to select the appropriate month for all project with a status in work. Is that possible?
Thanks in advance!

------------------------------
Trish Wehrle
------------------------------
Formulas and functions
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    If you just want a report, the easiest way is to make a Report with two <ask the user> date filters.
    It's been a while since I encountered this, but here are the steps in your thought process.

    This can be done by making a report with two <ask the user> date filters.

    The first filter will be where [End Date] is On or after <ask the user>  (answer in your example will be Feb 1)
    The second filter will be where [Start Date] is on or before <ask the user> (answer in your example will be Feb 29)

    I think that will work.  But the mental problem as I recall is that the human answering these questions (which may very well be you!) will be confused.


    As I recall, while counter intuitive,  the solution is to make a pair of formula date field called

    [Start Range Date] with a formula of [End date] and then 
    [End Range Date] with a formula of [Start date] 

    Then put those into our ask the user filters instead in place of [End Date and Start Date]

    That way the questions which will be asked will make sense to the user.

    Does this hurt your head??    Well, just try it and you will see what I mean. 


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------