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:

=Today’s Date03/07/2019
=+7Seven Days from Today03/14/2019
=–7Seven Days Before Today02/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:

EntryWhat it MeansExample
0The current day of the monthThe 7th
1The first day of the monthThe 1st
15A specific day of the monthThe 15th
31[1]The last day of the month31st

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

For example:

EntryWhat it MeansExample
0The current monthMarch
–1One month agoFebruary
+1Next monthApril
1The first month of the yearJanuary

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.

EntryWhat It MeansExample
15A specific year2015
0The current year2019
–1One year ago2018
+1Next year2020

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