Calculating rental days in a rental business (date math)

•How to use IF() function logic to control where calculations are done (i.e., on which rows)

A Simple Date Math Example

For basic information on handling dates and times, see the topic Dates, times, and date calculations.

Suppose an equipment rental business charges for equipment based on the number of days the customer had it out on rental. They have customized the Invoice form in QuickBooks, adding columns to hold the OUT date and IN date. Now they want FormCalc SST to calculate the number of rental days based on dates entered in those two columns, with the calculated result placed in the Quantity column.

To set up FormCalc SST for this, we would take a snapshot of the Invoice, indicate the column types of columns in the Detail area, then enter a formula in the Quantity column like the following

Here's a partial view of the Snapshot page in FormCalc SST, with the OUT Date and IN Date columns formatted as dates and the Quantity column showing the formula.

Here is another view. The Quantity column shows the formula's results, and below the formula cell the Sample formulas and data rows show a range of sample results (based on the sample data in those rows).

Switching to QuickBooks, then pressing the FormCalc SST hotkey (F11) twice to process the form, gives this result:

Having verified that this FormCalc SST setup works as planned, we could use it on any Invoice where the same kind of calculation is desired.

A real-world rental business may need more complex calculations than shown here—like maybe calculating the number of days and half days an item was out on rental. That is something FormCalc SST is fully capable of doing, but it is beyond the scope of this simple example.

"Nothing" vs. a "Blank" Result

Suppose this same rental business also sells industrial cleaning supplies—mops, buckets, floor buffer pads, cleaning solutions, etc. On invoice lines where cleaning supplies are entered, quantities need to be entered directly in the Quantity column, not calculated by FormCalc SST. In fact, the FormCalc SST calculation described above would cause a problem on rows where cleaning supplies are entered. Sincethose rows have no dates in the OUT Date and IN Date columns, FormCalc SST would calculate a 0 (zero) result for them...overwriting the user-entered Quantity!

Overcoming this problem is simple. FormCalc SST is designed so that it will not write a "nothing" result to QuickBooks. So the formula needs to be enhanced a bit to use the IF() function in a way that says "If dates have been entered, calculate the Quantity, but if they are missing don't write anything in the Quantity column." Here is a "new and improved" formula to be entered in the Quantity column, in cell H18:

Breaking this formula down into its parts, it says that if cell F18 is a number, then calculate the result as G18-F18, otherwise the result is "" (nothing).

There is a big difference between "nothing" and a blank space. The formula described above specifies nothing as ""—two quotation marks with nothing between them. Sometimes though, you may want a formula to write a "blank" or "space" back to QuickBooks, which is not the same as nothing! In that case, you would specify the result as " "—two quotation marks with a space between them.

If we change the formula at cell H18, then use process an Invoice which contains both rental items and cleaning supplies, FormCalc SST will calculate a quantity for just the rental item rows.