
Calculate the date a week later 
Create a formula date type field which displays the date that's a week later than the value in the Start Date field. 
Dates 
[Start Date] + Days (7) 
Take the value in the Start Date field and add seven days to it. 
Date 


More on this solution... 

Automatically complete the Territory field, based on who the salesperson is. 
Based on the value in the user field Salesperson, display the correponding sales territory in the Territory field. 
Conditional (If  Then) 
If([Salesperson]=ToUser("baker@example.com"), "Western", "Eastern") 
Take the email address baker@example.com and convert it to the user value connected with that email account (you can use a user name instead of an email address). If the value in the Salesperson field is that user, then display the word Western, otherwise, display the word Eastern. 
Text 
Tip: Form rules can also automatically populate fields based on other values.
Want to set this up for multiple salespeople and territories? Use the Case() function instead. Read how in the next section. 

More on this solution... 

Color rows based on who a user is 
You want to color code rows based on the user in the Assigned To field. 
Row Colorization 
If([Assigned To]=ToUser("baker@example.com"), "pink", "") 
Take the email address baker@example.com and convert it to the user value connected with that email account (you can use a user name instead of an email address). If the value in the Assigned To field is that user, then color the row pink, otherwise, don't color it. 
none  view builder formula 
Don't forget to enclose the email address or user name in quotation marks. 

More on this solution... 

I need a formula that returns the reporting period month. Any date after the 18th of the month will return a reporting period of the following month 

Dates 
If(Day([Date]) <= 18, Case((Month ([Date]))1, 1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December"), Case(Month ([Date]), 1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December"))

If the day of the month entered into the [Date] Field is less than or equal to 18, then subtract one from the month of the date field and based on the month number return the matching name of month.
Else if the day of the month entered into the [date] field is greater than 18 then return the month of the [date field], and based on the month number return the matching month name.

Date 


More on this solution... 

Calculate the number of weeks into the year a date is 
In Excel I can use the WeekNum function to calculate how many weeks into the year a particular date is. How do I do this in QuickBase? 
Dates 
Int(DayofYear([datefield])/7+1)

Find the day of the year that the date field is (in other words how many days into the year the date is). Then divide by seven to get the number of weeks into the year. Add one. Then show the integer only (in other words, leave off any fractional values that follow the decimal point). 
Numeric 
There is no week 0, so the first week is 1. That's why you add 1.
This formulas returns the "Absolute Week Number" which counts weeks starting from January 1st, no matter what day of the week it is. In other words, Jan 1st through 7th is always week one. If Jan 1st is a Tuesday, than each week of the year will be Tuesday through Monday and tallied accordingly. 
If you'd like to calculate the week number, but specify that each week begin on a Sunday (in other words, week one always ends on the first Saturday of January and following weeks are tallied based on that premise) try this formula instead:
Int(DayOfYear(Days(DayOfWeek(FirstDayOfYear([MyDate])))
+ [MyDate])/7+1) 
More on this solution... 

Set conditions across fields: If a user appears in any one of three fields show the record on a report 
Report needs to show records where the current user appears in any of three fields: Owner, Author or Reviewer 
Conditional (If  Then) 
Create a custom formula column (type checkbox) with the following formula:
[Owner]=User() or
[Author]=User() or
[Reviewer]=User()

If the
Owner field contains the value that is the current user or
Author field contains the value that is the current user or
Reviewer field contains the value that is the current user
then turn on the checkbox 
Checkbox 
Because the custom column is a checkbox type field, you don't need to use an If() function to set the condition. If the conditions listed are met, the value in the custom column is automatically Yes (in other words, the checkbox is turned on).
Once you create this formula for the custom column of your report, you must set the filtering section to take advantage of it. Select Custom Column is and then type in the word YES.
Read more about Custom Formula columns here: https://www.quickbase.com/help/custom_column_in_view.html 

More on this solution... 

Dynamically adjusting successor start dates from predecessor changes 
Related to Project Management application 
Calculation 
If( ([Status]<>"Completed"),WeekdayAdd([Start], [Duration]),
ToWorkDate([Actual Finish Date]) )
The predecessor field (under properties) needs to be changed from formula builder to TypeIn. 
If the task is not completed, it calculates the expected end date (by adding the amount of time it will take to when it starts), and if the task is completed, then it uses the date that it was actually finished, as entered by the user completing the task. 
Workdate 


More on this solution... 

Setting a sales commission based on sale price 
Create a formula for the Sales Commission field that calculates commission based on sale price in the Total field 
Conditional (If  Then) 
If([Total]>=100 and [Total]<250, 25,
[Total]>=250 and [Total]<1000, 50,
[Total]>=1000, 100, 0) 
If total is greater than or equal to 100 and less than 250, then display the value 25.
If total is greater than or equal to 250 and less than 1000, then display the value 50
If total is greater than or equal to 1000, then display 100
Otherwise (if none of the above conditions are met) display 0. 
Numeric 


More on this solution... 

Calculate a task's finish date, based on the start date and number entered in the Estimated # of Days field. 
Adding a numeric value (Estimated # of Days) to a date value doesn't work. 
Type Conversion 
[Actual Start Date]+ Days([Estimated # of days]) 
Convert the number in the Estimated # of days field into a duration that represents a number of days and add it to the Actual Start Date. 
Date 
The Days() function converts the numeric value into a Duration value. 

More on this solution... 

Need to see the day of the week for a date. 
If only the day of the week is needed and not the whole date, then this formula can be used. 
Dates 
Case(DayOfWeek([YourDateFieldHere]), 0, "Sunday",
1, "Monday",
2, "Tuesday",
3, "Wednesday",
4, "Thursday",
5, "Friday",
6, "Saturday") 
Note that the DayofWeek() takes a date, and not a workdate, so if the field is a workdate field, then you must convert it to date using ToDate(). 
Text 


More on this solution... 

Find duration between two dates 
Find the length of time between the date a record is created and the date the record's marked complete. 
Dates 
ToDays([Actual Finish Date]  ToDate([Date Created])) 
Take the value in the Date Created field (Date / Time type field) and convert it to a date value. Then subtract that date from the Actual Finish Date. Convert the resulting duration to a number of days. 
Numeric 
You must convert the this formula result to days to make it data type numeric.
If your result field type if formuladuration instead try:
[Actual Finish Date]  ToDate([Date Created]) 
If both dates live in Date type fields you can use a much simpler formula: [Actual Finish Date]  [Start Date] 
More on this solution... 

Find duration between a date and today 
Find the length of time between a date and the current day's date (today). This is handy for finding the number of days until a planned finish date, for example. 
Dates 
ToDays([Planned Finish Date]  Today()) 
Subtract today's date from the Planned Finish Date. 
Numeric 
If you want to display the result in a Duration type field instead, then you don't need the ToDays() function. Use this formula instead:
[Planned Finish Date]  Today() 
If your result field is a Duration type field, try this formula instead: [Planned Finish Date]  Today() 
More on this solution... 

Limit the text that an append field displays 
If you include an append field in a view, it often takes up a lot of room because it contains so much text. If you want, you can display only the last entry in the append field. 
Text 
Right([History],"[") 
Within the History field, take and display all characters to the right of the rightmost [ character. 
Text 
Use this solution if your append field is set to APPEND entries. If the field is set to prepend entries instead, try: Part([History],2,"[") 
If your append field is set to PREPEND text, use this formula instead:
Part([History],2,"[") 
More on this solution... 

Convert dates to European format 
Is there any way to change the default date format in my Date Identified field to the european DD/MM/YYYY? 
Dates 
Left(NotLeft(ToText([Date Identified]),""),"")& "" &
Case(Left(ToText([Date Identified]),2),
"01","Jan","02","Feb","03","Mar","04","Apr","05",
"May","06","Jun","07","Jul","08","Aug","09","Sep",
"10","Oct","11","Nov","12","Dec")& ""
& Right(ToText([Date Identified]),"") 

Text 
You can't do this within a date field. You must create a formula  text type field to display dates in this format. 

More on this solution... 

Calculate a date 1 year ago 
Return a date that's exactly one year before the value in an existing date field (named Date). 
Dates 
AdjustYear([Date],1) 
Take the value in the Date field and subract one from the year. 
Date 
Note that you can use the AdjustYear() function to go forward in time too. 

More on this solution... 

Extract day and month from a date 
You want to display only the day and month without the year. 
Dates 
Left(ToText([Date]),5) 
Convert the value in the Date field to a text type value. Then extract the leftmost 5 characters. 
Text 
If the value in the Date field is 07081967, this formula returns 0708. Note that this formula produces a text type value. 

More on this solution... 

Calculate the date three business days later 
Create a formula date type field which displays the date that's three business days after the value in the Start Date field. 
Dates 
WeekdayAdd([Start Date],3) 
Take the value in the Start Date field and add three weekdays to it. 
Date 
This formula works when the value in the Start Date field is a date type value. If the Start Date field is a Workdate type field, do one of the following:
 use the ToDate() conversion function: ToDate(WeekdayAdd([Start],3))
or
 Create a Formula Workdate type field to store results instead of a Formula Date type field. 

More on this solution... 

Remove "the" from names to enable alphabetical sorting 
You have a list of organizations, some of which start with THE. You want to remove the THE so you can sort the list alphabetically. 
Text 
If(Contains(Left([Organization Name],3),"The"), NotLeft([Organization Name],4), [Organization Name]) 
If the Organization Name field's leftmost three characters are "the" then remove the four leftmost characters (Why specify 4 instead of 3? To remove the space after "the"). Otherwise (if there's no "the") just show the organization name. 
Text 


More on this solution... 

Highlight overdue tasks in pink 
You want to color code rows based on the Due Date field. 
Row Colorization 
if ([Due Date] < Today(), "pink", "") 
If the value in the Due Date field is less than (e.g. earlier than) today, then color the row pink, otherwise, don't color it. 
none  view builder formula 


More on this solution... 

Color each row differently, based on the status of each task. 
You want to color code rows based on the value in the Status field. 
Row Colorization 
case([Status], "Not Started", "#FFCC99",
"Started", "#99FFFF",
"In Progress", "#FFFFCC",
"On Hold", "#9999CC",
"Completed", "#99FF99", "") 
If the value in the Status field is "Not Started," then color the row a shade of orange (#FFCC99).
If the value in the Status field is "Started," then color the row a shade of blue (#99FFFF).
If the value in the Status field is "In Progress," then color the row a shade of yellow (#FFFFCC).
If the value in the Status field is "On Hold," then color the row a shade of purple (#9999CC).
If the value in the Status field is "Completed," then color the row a shade of green (#99FF99)
If the value in the Status field is none of these, don't color it. 
none  view builder formula 
This formula doesn't use the if() function. Because you want to set multiple conditions on one field, it's more efficient to use the case() function. 

More on this solution... 

Calculate the number of months until a payoff date 

Dates 
((Year([Payoff Date])  Year(Today()) )* 12) + (Month([Payoff Date])  Month(Today()) ) 
Take this year (that today's date occurs in) and subtract it from the year listed in the Payoff Date field. Then multiply that number of years by 12 (to get months). Take that number of months and add it to the number of months that results from subtracting this month from the month listed in the Payoff date field. 
Numeric 
You're really calculating the number of months in two steps. On the left side of the plus sign, you're figuring out how many years until the payoff date and converting that into months. On the right side of the plus sign, you're figuring out the difference in months. The plus sign then adds those figures together. 

More on this solution... 

Calculate an expiration date 
Calculate an expiration date based on a contract start date and a period of months. 
Dates 
AdjustMonth([Start Contract Date], [Contract Length in Months]) 
Take the date in the Start Contract Date Field and add the number of months in the Contract Length field. 
Date 
Contract Length is a numeric field which lists a number of months 

More on this solution... 

string together text snippets 
Concatenate (string together) text in two different fields. For example, create a field that lists a contacts full name by combing values from the First Name and Last Name fields 
Text 
[First Name] & “ “ & [Last Name] 
Display the value in the First Name field. Display a space. Display the value in the Last Name field. 
Text 
Note: To create a space between the names, this formula inserts a text literal. QuickBase displays whatever characters appear between a set of double quotesin this case, a space. 

More on this solution... 

Calculate the date one week from today 

Dates 
Today() + Days(7) 
Display the date that is today plus 7 days. 
Date 


More on this solution... 

Add up the number of hours worked in a week. 
Sum the values of hours entered for each day of the week. 
Calculation 
Nz([Mon]) + Nz([Tues] + Nz([Wed]) + Nz([Thurs]) + Nz([Fri]) 
Return the value in the Mon field. If the Mon field is empty (null) then return zero. Add that to the value in the Tues field. If the Tues field is empty (null) then return zero. Add that to the value in the Wed field, and so on. 
Numeric 
You'd use Nz here instead of IsNull, because in order to add these values together, QuickBase needs the result to be a number. Nz generates a zero for a null, which the program can use in the calculation. 

More on this solution... 

Display a value that depends upon the value in a percent complete field 
Show status "not started" "in process" or "completed" based on the value in the percent complete field. 
Conditional (If  Then) 
Case(true,
[percent complete]=0,"not started",
[percent complete]<1,"in process",
[percent complete]=1,"complete","") 
If the value in the percent complete field is zero, the display the text "not started." If the value in the percent complete field is less than 100%, then display the text "in process." If the value in the percent complete field is 100%, display the text "complete" 
Text 
A numeric percentage field shows values in percent format, like 50%, 75% and so on. But their real value is the exact mathematical representation of percent, which is always a portion of the whole number, one. For example, 20% is really.2 and 3% is really the number .03. 

More on this solution... 

Create a view that contains records for the "current month" only 

Conditional (If  Then) 
If(Month([Date])=Month(Today()), "Current Month","Not Current Month") 
If the month in the Date field is the same month as today, then display the text "Current Month" otherwise display the text "Not Current Month. 
Text 
Use this formula to create a view. You can feature the text field in the view to see which records say "Current Month." You can also use this custom formula field in the view's criteria. In other words, design the view so that you only see those records that occur in the Current Month. Read more about using custom columns in views here: https://www.quickbase.com/help/custom_column_in_view.html
See also: https://www.quickbase.com/db/6mztyxu8?a=dr&r=n8&rl=xgi 

More on this solution... 

Show records for which events have occurred in the last week 
Show records where any one of several date fields contains a date that occurred in the last week 
Conditional (If  Then) 
If(ToDays(Today()[Sales Presentation])<7,true,false) or
If(ToDays(Today()[FollowUp Meeting (2)])<7,true,false) or
If(ToDays(Today()[FollowUp Meeting (3)])<7,true,false) or
If(ToDays(Today()[FollowUp Meeting (4)])<7,true,false) or
If(ToDays(Today()[STR Meeting])<7,true,false) 
If you subtract the date in the Sales Presentation field from today and it's greater than seven, turn the checkbox field on (true), otherwise turn it off (false).
Each line of the formula beginning with "If" repeats these same conditions for each field listed. Each If/Then condition is joined by an "or" operator. This means that if the condition is met in any field, QuickBase will turn on the checkbox field. 
Checkbox 
Use this formula to create a view. You can feature the checkbox field in the view to see which records have it checked and which don't. You can also use this custom formula field in the view's criteria. In other words, design the view so that you only see those records where this checkbox field is on. Read more about using custom columns in views here: https://www.quickbase.com/help/custom_column_in_view.html 

More on this solution... 

Find records that occur before the current month 

Dates 
[Start Date]< FirstDayOfMonth(Today()) 
Start date is before the first day of the month in which today occurs 
Checkbox 


More on this solution... 

create a conditional salutation that can contain one or two names depending on content 
If the First Name 2 field contains a name, then string it together with the value in the First Name 1 field to create a salutation. If not, then just show the name in First Name 1. For example "Mary & Joe" Or if there's no text in First name 2, just "Mary" 
Text 
If([First Name2]="",[First Name 1], [First Name 1] & " & " & [First Name 2]) 
If First Name 2 is empty, then display the value in the First Name 1 field. Otherwise, display the value in the first name 1 field then display a space, an ampersand and a space followed by the value in the First Name 2 field. 
Text 


More on this solution... 

Extract and display only the most recent entry from an append field 
Append fields often take up too much room in views. Use a formula field to show only the most recent entry. 
Text 
Right([comments],"[") 
Return the rightmost text from the comments field that follows the last occurence of the [ character. 
Text 
This solution is for an append field that appends new entries. If the field were set to prepend new entries, the solution would be: Part([comments],2,"[") 

More on this solution... 

If a field is not blank (null), then display a value in another field 
Calculate the Revenue field, only if your staff has entered a date in the Submitted for Billing field. 
Conditional (If  Then) 
Design the actual Revenue field using the following formula:
If(not IsNull([Submitted for Billing]),[Revenue Forecast]) 
If the Submitted for Billing field is not empty (or null), then display the value from the Revenue Forecast field. 
Text 


More on this solution... 

Set status based on whether or not another field is checked. 
Automatically set the Status field to "Complete," when a staff member enters a date in the Completion Date field. 
Conditional (If  Then) 
Design the Status field using the following formula:
if(isnull([Completion Date]), "Pending", "Complete") 
If no one's entered a value in the Completion Date field (in other words that field is null) then display the word "Pending." If not, display the word "Complete." 
Text 


More on this solution... 

Calculate the number of checkboxes that a user turned on 
Total the boxes checked (turned on) within a given record 
Calculation 
ToNumber([Checkbox1]) + ToNumber([Checkbox2]) 
Convert the value in Checkbox1 to a number and add it to the value in Checkbox 2. Include as many checkbox fields as necessary 
Numeric 
The ToNumber formula returns a 1 for true or yes and a 0 otherwise. 

More on this solution... 

Convert a value representing time in minutes into seconds 
When I enter a time in minutes and seconds (ie. 3.42) I need to be able to convert these to seconds. 
Time 
ToNumber(Left([Minutes], ".")) * 60 + ToNumber(Right([Minutes], ".")) 
The Left function will return all the text up to but not including the period. In the example, that is 3. Since it is a text value, convert it into a number using the ToNumber function and then multiply the result by 60 to get seconds.
Do the same to extract the text after the period using the Right function. Finally, we add it up to get the total seconds  in this case it should be 3*60 + 50 = 230 seconds. 
Numeric 
Use a text field to store the number (like 3.42) since three minutes and 42 seconds is not the same thing as 3.42 seconds. 

More on this solution... 

Calculate how many fields are empty 
I need to create a field that counts the number of field that are non blank. The field that I need to count are labeled Model number 1, Model number 2 etc... 
Calculation 
ToNumber(IsNull([Model number 1])) +
ToNumber(IsNull([Model number 2])) 
IsNull returns a yes or no result, which when you convert it to a number using the ToNumber() function, becomes a one for yes or a zero for no. Insert a + operator to add all values together. Add as many additional fields as you need. 
Numeric 
This solution assumes that the Model Number fields are numeric. If those fields are text type fields, the IsNull() function wont work. Try something like the following instead:
ToNumber(If (Length([Model number 1]) > 0, true, false) +
ToNumber(If (Length([Model number 2]) > 0, true, false) 

More on this solution... 

Calculate the number of months that the date of last payment is past due 
I want to show the number of months overdue, but include complete months only, leaving off possible partial months. 
Dates 
Floor ( ToDays (Today()  [Date of Last Payment]) / 30) 
Subtract the Date of Last Payment from Today. ToDays converts that duration to a numeric value of days. Divide that number by 30 and return the the number of complete months that have passed. 
Numeric 
Floor() function returns the largest integer that's less than the number in question. For example, Floor(2.4) would return 2. Likewise, Floor (2.7) would return 2. 

More on this solution... 

Calculate project duration if project is completed 
If [Status] is "Completed", figure the duration based on [End Date], but if the status is anything else, figure duration on today's date. 
Conditional (If  Then) 
IF(([Status]="Completed"),[End Date][Start Date],Today() [Start Date]) 
If the value in the Status field is "Completed" then show the result of subtracting the Start Date from the End Date. Otherwise, show the result of subtracting the Start Date from today. 
Duration 


More on this solution... 

Tally duration fields even if one or two are empty (null) 
Total Actual Duration  [Actual Duration] + [Actual Duration 1] + [Actual Duration 2] 
Calculation 
Nz([Actual Duration]) + Nz([Actual Duration 1]) + Nz([Actual Duration 2]) 
Return the value in the Actual Duration field. If the Actual Duration field is empty (null) then return zero. Add that to the value in the Actual Duration 1 field. If the Actual Duration 1 field is empty (null) then return zero. Add that to the value in the Actual Duration 2 field, and so on. 
Duration 
You'd use the Nz() function here instead of IsNull(), because in order to add these values together, QuickBase needs the result to be a number. Nz generates a zero for a null, which the program can use in the calculation. 

More on this solution... 

View records from the previous week only 
How do I create a view that on any day of the week I can view records dated from the previous week? 
Dates 
If([Date]>FirstDayOfWeek(Today()Days(7)) and
[Date]< LastDayOfWeek(Today()Days(7)),
true,
false) 
Subtract 7 days from today. Take the resulting date (let's call it "one week ago date") and calculate the first day of its week. If that is greater than the value in the Date field and If that date is less than the last day of the week in which "one week ago date" falls, the result is true (checkbox is on) otherwise the result is false (checkbox is off). 
Checkbox 
You can create a formula  checkbox type field to hold this formula or just use the formula within the report (view) builder page. In this case, also select checkbox as the type, then within the Matching Criteria section, specify that <custom column> is yes (type in the word yes). 

More on this solution... 

Calculate the date of the last Friday before a Due Date 
In each record, I want to calculate what date is the Friday that comes before each Due Date 
Dates 
PrevDayOfWeek([Date Due], 5) 
Take the value in the date due field and figure out the date of the previous week's Friday. 
Date 
5 represents Friday within the PrevDayOfWeek() function. 

More on this solution... 

Show only records where any checkbox is turned on 
Formula to create a view that shows records where [checkbox 1] is turned on OR [checkbox 2] is on OR [checkbox 3] is on and so on 
Conditional (If  Then) 
If(([F1] or
[F2] or
[F3])=true, true,
false) 
If F1 or F2 or F3 checkboxes are turned on (True), then this formula returns True (yes), otherwise it returns false (no). 
Checkbox 
You'd use this formula to create a "custom column" in the view builder. The custom column type is "checkbox." Then set the view's matching criteria to <custom column> = Yes. When you do so, your view shows only those records that meet the condition of the formula. For more on using formulas to create views and reports, please read: https://www.quickbase.com/help/custom_column_in_view.html 

More on this solution... 

Calculate how many gallons are used per hour 
I want to divide gallons consumed (numeric field) by hours of operation (duration field). 
Type Conversion 
[gallons consumed]/ToHours([hours of operation]) 
Convert the duration in the hours of operation field to hours. Then divide the value in the gallons consumed field by that number of hours 
Numeric 
You can't divide a number by a duration. So you must convert the duration into a number using the ToHours() function. 

More on this solution... 

Find the duration between two date fields of different types 
Actual Finish is a date field and Actual Start is a workdate field. How can I find the duration between them without getting a type mismatch error? 
Type Conversion 
[Actual Finish]ToDate([Actual Start]) 
Convert the value in the Actual Start field to a date type value. Then subtract that date from the Actual Finish date 
Duration 


More on this solution... 