Forum Discussion

Qrew Cadet

4 years ago

Bi Monthly Billing Calculated Field based off Date chosen

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

reports and charts

MarkShnier__You
4 years ago
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
------------------------------

MarkShnier__You

Qrew Legend

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

RyanLocke1

Qrew Cadet

4 years ago

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

MarkShnier__You
Qrew Legend
4 years ago
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
------------------------------
- RyanLocke1
  Qrew Cadet
  4 years ago
  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
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    4 years ago
    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
    ------------------------------