Date formulas make running TBO and CB reports easier

geralt / Pixabay

In an earlier post, I talked about some easy shortcuts for entering dates in Trams and ClientBase. In this post, I want to share some more advanced ways you can use date formulas.

Date formulas are a great way to quickly calculate dates. These are particularly helpful when saving queries to run reports on a regular basis or if you just want to quickly enter today’s date.

One of my favorite shortcuts for entering dates is date formulas. In both programs, you can use the equal sign (“=”) to tell them to calculate the date automatically.

The simplest use for date formulas is to enter today’s date. By just typing “=”, the programs will enter today’s date.

You can also quickly calculate days before or after today by using the plus (“+”) or minus (“-”) signs.

For example:

Entry Description Example
= Today’s Date 03/07/2019
=+7 Seven Days from Today 03/14/2019
=–7 Seven Days Before Today 02/28/2019

More advanced dates formulas

Using date formulas is particularly helpful when creating saved queries for searching and reporting.

Date formulas consist of four parts and take the format =DD,MM,YY. The *equal sign (“=”) tells TBO and CB that you are using a formula instead of a specific date.

The DD section tells the program that you are entering a relative date.

For example:

Entry What it Means Example
0 The current day of the month The 7th
1 The first day of the month The 1st
15 A specific day of the month The 15th
31[1] The last day of the month 31st

The MM section tells the program what month you want to use.

For example:

Entry What it Means Example
0 The current month March
–1 One month ago February
+1 Next month April
1 The first month of the year January

The YY section tells the program what year you would like to use. If you leave the year blank, it is always relative to the current date.

Entry What It Means Example
15 A specific year 2015
0 The current year 2019
–1 One year ago 2018
+1 Next year 2020

So, how does this help a Trams or ClientBase User?

Many Trams and ClientBase users want to run similar reports on a regular basis. For example, let’s say you want to run a report for invoices issued during the previous month. Instead of creating a new query each time, you can save your search and have TBO or ClientBase recalculate the dates automatically.

Instead of typing in the specific dates, try this:

In the “Date From” field, type =1,–1 and in the “Date To” field, type =31,–1. If I ran this search anytime in March, it the dates would automatically calculate to “02/01/18” to 02/28/18″. Notice that TBO and CB know there are only 28 days in February, so we didn’t have the change the formula to account for that.

The Trams website has a great document that demonstrates how you can use various saved queries to better manage your work.
In ClientBase, users can use date formulas to save queries in all of the manager tabs (Profiles, Res Cards, Activities and Inventory).

In TBO, defaults can be set for each report, or users can use date formulas when creating report batches. I’ll discuss batching reports in TBO in a future blog entry.

  1. Trams and ClientBase are smart enough to know how many days are in a particular month. You can always use the number “31” to indicate the last day of the month. If the month has 28, 29 or 30 days, it will automatically use that day.  ↩

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top