## Reply to comment

If your boss is like my boss, she wants to know what I accomplished this week, what I accomplished last week, and what I plan to accomplish next week. Building Reports in QuickBase to help you manage this reporting requirement can be a little tricky, but with some well-built formula fields in your data table, you can get it done.

First, you need to have at least one date field in your table that we can compare to today and determine if that date occurs last week, this week, or next week. In my process I have a [Due Date] field and a [Date Completed] field that serve this purpose. I'll refer to these field names in my examples.

Next, you need to use a few formula functions to craft the formula fields that will make your reporting easy. In this case, we'll be using the "If" function to determine if a condition is true. We'll use the If function in a Formula Checkbox field. Since the Checkbox field is a binary field, true or false, yes or no, we'll be able to evaluate a set of conditions in the formula and determine if they are true.

Let's start with a field that will help us determine if something is Due This Week.

Create a field in the table and label it: "Due This Week".

Make the field type: "Formula - Checkbox"

Edit the field properties and enter this formula into the formula area:

If(FirstDayOfWeek([Due Date]) = FirstDayOfWeek(Today()), true,

false)

* In that example we're using the FirstDayOfWeek function and comparing the first day of the [Due Date] week to the first day of today's week. To capture today's date in a formula you can use the argument: "Today()"

The formula for Due Next Week would be:

If(FirstDayOfWeek([Due Date]) = FirstDayOfWeek(WeekdayAdd(FirstDayOfWeek(Today()),5)), true,

false)

* In that case I added five weekdays to today's date to get next week's first day.

How about Due Last Week?

If(FirstDayOfWeek([Due Date]) = FirstDayOfWeek(WeekdayAdd(FirstDayOfWeek(Today()),-5)), true,

false)

* Add a negative number of weekdays to look backwards.

And finally, try Due or Completed This Week.

If(FirstDayOfWeek([Due Date]) = FirstDayOfWeek(Today()), true,

FirstDayOfWeek([Date Completed]) = FirstDayOfWeek(Today()), true,

false)

* In that formula, if the first condition evaluates to false, it moves on to try the second condition, and if either condition is true, the checkbox will be checked.

The last step of this process is to create views that will match records where one of your formula checkbox fields is checked. To create a View of items that are Due This Week, set the matching in your Report Builder to: [Due This Week] IS "checked."

For Due next week, create a new view and set the matching to return only records where [Due Next Week] is "checked", and so on.

- 46153 reads