Discussions

 View Only
  • 1.  Bi Monthly Billing Calculated Field based off Date chosen

    Posted 08-05-2021 21:34

    Good Evening,

    I am new to quickbase and am trying to figure out a way to set up a filter where a user selects a date and then a report will filter for their current billing period. We bill bi-monthly 1-15th and 16th-30th or 31st.

    I was hoping a user could enter a billing record and chose 08/05/2021 and once they save and close the record their dashboard will have a "Current Billing Period" report so they only see the entries for the Current Billing Period whatever date it falls in, and once we get to August 16th the report will refresh with just those entries. 

    Is there a good way to do that? 

    Or a calculated date field that would some how make a string like 08-01-2021 - 08-15-2021, based on the date would be great too.

    Thank you,

    Ryan



    ------------------------------
    Ryan Locke
    ------------------------------


  • 2.  RE: Bi Monthly Billing Calculated Field based off Date chosen

    Posted 08-06-2021 19:31
    What is the value you're calculating the timeframe on? Is it all records due on the 15th or the 31st or is it based on some form of date criteria already- i.e. contract start date, user input deadline?

    It seems like when building a table you should be able to set criteria similar to the below based on what you're reference field is (i'm using deadline as an example):
    Deadline = during= current= month
    Deadline=during= past= 15 = days

    To have on the homepage dashboard, it won't show a table that requires a "user select" date, you'd need to create a shortcut button to the full table if you want to sort based on something that the user selects uniquely each time.
    You could however show the above formula and also add a criteria of something like "sales person= the current user" and it would show only records for that user with the additional criteria.

    Hope I understood what you meant!

    ------------------------------
    Korrie Clark
    ------------------------------



  • 3.  RE: Bi Monthly Billing Calculated Field based off Date chosen
    Best Answer

    Posted 08-06-2021 21:51
    Edited by Mark Shnier (Your Quickbase Coach) 08-06-2021 22:02
    Ryan

    I'm going to assume your field is calling [Invoice Date].  I'm typing on an ipad from a hotel room with a beer, so hopefully this will work. 


    Try this. Make two fields.  The first is called [Billing Period Start of Invoice Date]

    var date MyDate = [Invoice Date]; 
    var number DayNumber = IF(Day($MyDate) <= 15, 1, 16);

    Date(Year($MyDate), Month($MyDate), $DayNumber)

    // the formula above will return the date of the start of the Billing Period. 

    Then duplicate the field and call it [Billing Period Start of Today]

    but change the date field to be Today()

    var date MyDate = Today();
    var number DayNumber = IF(Day($MyDate) <= 15, 1, 16);

    Date(Year($MyDate), Month($MyDate), $DayNumber)


    now easy peasy, just filter the Report where those  two fields are equal. 

    Post back if you have have any problems. I'm cycling so I will be slow to respond. 




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



  • 4.  RE: Bi Monthly Billing Calculated Field based off Date chosen

    Posted 08-06-2021 22:00
    Thank you, Mark! Such a great tip! I'm really going to like this Quickbase Community! Thank you for the help!

    ------------------------------
    Ryan Locke
    ------------------------------



  • 5.  RE: Bi Monthly Billing Calculated Field based off Date chosen

    Posted 08-18-2021 12:03
    Hey Mark,
    That worked perfectly, I did have a question. Do you know a good way to get the Billing Period End Date? I am getting tripped up on if it ends in 30 or 31, and also Feb. Thank you!!!

    ------------------------------
    Ryan Locke
    ------------------------------



  • 6.  RE: Bi Monthly Billing Calculated Field based off Date chosen

    Posted 08-18-2021 12:25
    There is a convenient function call LastDayOfMonth, so for example LastDayOfMonth(Today()) would be August 31st because as i type this the date is August 18th.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Bi Monthly Billing Calculated Field based off Date chosen

    Posted 08-27-2021 10:53
    Hey Mark,

    I was working on the code you sent below. I used the Billing Period Start Date formula you sent, and I wanted a Billing Period End Date, you had given me the 'LastDayOfMonth' function but was having trouble working it in because it's a Date field. For my else statement in my end of the billing period, I put 99 and it will actually put the last day of the month. I just stumbled upon it. I am green to Quickbase so you can laugh if it's commonplace, but wanted to see if you ever use it or thoughts?
    var date MyDate = [Date];
    var number DayNumber=If(Day($MyDate)<=15,15,99);
    Date(Year($MyDate),Month($MyDate),$DayNumber)

    ------------------------------
    Ryan Locke
    ------------------------------



  • 8.  RE: Bi Monthly Billing Calculated Field based off Date chosen

    Posted 08-27-2021 11:06
    try this


    var date MyDate = [Date];

    var number DayNumber=If(Day($MyDate)<=15,15,Day(LastDayOfMonth([Date]));
    Date(Year($MyDate),Month($MyDate),$DayNumber)

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------