# Date Calculation Cheat Sheet

For
some time now, I’ve maintained a cheat sheet for various calculated fields that
I find myself using (or sharing) regularly. I recently noticed how many of
these calculations were date-related. It occurred to me that it would be nice
to have a single resource that contains all of these date calculations, making
them available for other to use, without the need for reinvention. So, that’s exactly
what I’m going to be sharing here today. My *Date Calculation Cheat Sheet*
contains 46 reusable calculation. In this blog, I’ll provide a brief
introduction and some example calculations, then show you how to use them in
your own work.

Tableau has tons of different date and time-related
functions for you to use. I’m assuming a familiarity with many of these already
so I won’t be talking about them in any detail. If you’re not familiar with
these functions, I recommend the following blog by Carly
Capitula of Interworks, which gives a good overview of most of them: Tableau
Essentials: Calculated Fields – Date Functions

*Note: If you want to skip the explanation of the various
calculated fields and jump right to the workbook, you can find it here: Date
Calculation Cheat Sheet. Just download it and copy the calcs you need.*

**The Calculations**

As noted, I’ll be sharing 46 calculated fields. Forty-two of these are
intended to return the following for the **previous**, **current**, or **next**
**week**, **month**, **quarter**, or **year**:

• The first and last day of each period

• The number of days in each period

• The number of weekdays in each period

The remaining 4 calculated fields will
give you the following:

• Decade

• Century

• Last of a Specific Day in a Month – For example,
the last Sunday of the month.

• Nth of a Specific Day in a Month – For
example, the third Wednesday of the month.

As the first 42 are all of a common
theme, I’m going to share the details of the month calculations here and you
can refer to the workbook for week, quarter, and year calculations. Note: These
calculations refer to a field called **Date** which could be any date or
parameter in your data.

**First & Last Day**

Let’s start by calculating the first
day of the month for the current month (the month of the **Date** field). To
do this, we’re simply going to truncate the date to the first day of the month.
Then, since DATETRUNC returns a date/time field, we’ll convert it to a date.

__Month – Current – First Day__

**DATE****(**

** ****DATETRUNC****(****'month'****, ****[Date]****)**

**)**

For the last day, we start by
truncating to the first day of the month. Then we add a month, giving us the
first day of next month. Then we subtract a day to get the last day of the
current month.

__Month – Current – Last Day__

**DATE****(**

** ****DATEADD****(****'day'****, -1, **

** ****DATEADD****(****'month'****, 1, **

** ****DATETRUNC****(****'month'****, ****[Date]****)**

** )**

** )**

**)**

To get the first day of *next* month, we truncate to
the first day of the moth, then simply add one month.

__Month - Next - First Day__

**DATE****(**

** ****DATEADD****(****'month'****, 1, **

** ****DATETRUNC****(****'month'****, ****[Date]****)**

** )**

**)**

For the *last* day of next month, we first truncate to
the first day of the month. Then we add two months, giving us the first day of
the month after next. We then subtract a day, giving us the last day of next month.

__Month -
Next - Last Day__

**DATE****(**

** ****DATEADD****(****'day'****, -1, **

** ****DATEADD****(****'month'****, 2, **

** ****DATETRUNC****(****'month'****, ****[Date]****)**

** )**

** )**

**)**

For the first day of the *previous* month, we first truncate
to the first day of the month, then subtract one month.

__Month -
Previous - First Day__

**DATE****(**

** ****DATEADD****(****'month'****, -1, **

** ****DATETRUNC****(****'month'****, ****[Date]****)**

** )**

**)**

And, to get the *last* day of the previous month, we truncate
the to the first day of the month, then simply subtract one day.

__Month -
Previous - Last Day__

**DATE****(**

** ****DATEADD****(****'day'****, -1, **

** ****DATETRUNC****(****'month'****, ****[Date]****)**

** )**

**)**

The calculations for weeks, quarters, and years are all
relatively similar to the above (with some slight difference), so I won’t show
them all here.

**Number of Days**

Now let’s take a look at the *number
of days* calculations. When dealing with months, I’ve observed people
attempt to perform these calculations using case statements by month, but those
have to account for leap year rules, which are more complicated than just every
four years. We’ll avoid the need for special logic, building calculations that inherently
handle leap years. Essentially, we’ll find the last day of the month (using the
formulas above), then get the day of that date. While we could simply reference
the calcs we’ve already created, I chose to make these calculations independent
of each other so that you can copy just the ones you need.

__Month - Current - Number of Days__

**DAY****(**

** ****DATEADD****(****'day'****, -1, **

** ****DATEADD****(****'month'****, 1, **

** ****DATETRUNC****(****'month'****, ****[Date]****)**

** )**

** )**

**)**

__Month - Next - Number of Days__

**DAY****(**

** ****DATEADD****(****'day'****, -1, **

** ****DATEADD****(****'month'****, 2, **

** ****DATETRUNC****(****'month'****, ****[Date]****)**

** )**

** )**

**)**

__Month - Previous - Number of Days__

**DAY****(**

** ****DATEADD****(****'day'****, -1, **

** ****DATETRUNC****(****'month'****, ****[Date]****)**

** )**

**)**

The Quarter and Year calculated fields are
a bit different than the Month calcs. For quarters, we calculate the number of
days between the first and last day of the quarter (and add one day). For year,
we get the *day of year* datepart of the last day of the year. The number
of days in a week is always seven, so there are no *Number of Days *calculated
fields for weeks**. **

**Number of Weekdays**

The number of weekdays in a period of
time is a bit more difficult to calculate than the number of days as it needs
to throw out weekends. Fortunately, Tableau has a great knowledge base article
for this: Calculating
Number of Weekdays Between Dates. I’ve leveraged this technique here. The
logic is a bit too much to cram into independent calculated fields, so each of
these has two helper calculations, which refer back to the first and last day
calcs in the first section. So, if you use these calcs, you need to also copy
the calcs on which these are dependent.

Let’s just look at the number of
weekdays in the current month. The first helper calculation finds the first
weekday in the month by shifting the first day forward as needed.

__Month - Current - Fist Weekday__

// Shift the date to
the next weekday.

**IF ****DATEPART****(****'weekday'****, ****[Month - Current - First Day]****) = 1 THEN **

// Sunday, so push 1 day forward to Monday

** ****DATEADD****(****'day'****, 1, ****[Month - Current - First Day]****)**

**ELSEIF ****DATEPART****(****'weekday'****, ****[Month - Current - First Day]****) = 7 THEN **

// Saturday so push 2 days forward to
Monday`

** ****DATEADD****(****'day'****, 2, ****[Month - Current - First Day]****)**

**ELSE **

// Already a weekday.

** ****[Month -
Current - First Day]**

**END**

Similarly, the second calculated field finds
the last weekday in the month by pushing the last day backward as needed.

__Month - Current - Last Weekday__

// Shift the date to
the previous weekday.

**IF ****DATEPART****(****'weekday'****, ****[Month - Current - Last Day]****) = 1 THEN **

// Sunday, so push 2 days back to Friday

** ****DATEADD****(****'day'****, -2, ****[Month - Current - Last Day]****)**

**ELSEIF ****DATEPART****(****'weekday'****, ****[Month - Current - Last Day]****) = 7 THEN **

// Saturday so push 1 day back to Friday

** ****DATEADD****(****'day'****, -1, ****[Month - Current - Last Day]****)**

**ELSE **

// Already a weekday.

** ****[Month -
Current - Last Day]**

**END**

With these, we can then calculate the number
of weekdays using the formula in the knowledge base article.

__Month - Current - Number of Weekdays__

**DATEDIFF****(****'day'****, ****[Month - Current - Fist Weekday]****, ****[Month - Current - Last Weekday]****) + 1**

**- 2 * ****DATEDIFF****(****'week'****, ****[Month - Current - Fist Weekday]****, ****[Month - Current - Last Weekday]****)**

The quarter and year calculations work in exactly the same
way as the month calcs.

**Miscellaneous**

Finally, I’ve included calculated fields to get *Decade*, *Century*, *Last
of a Specific Day in a Month*, and *Nth of a Specific Day in a Month*. Let's start with *Decade *and *Century*. Unfortunately,
there is no DATETRUNC option for these, so we just need to do a little math to
perform the truncation ourselves. For the decade, we really just want to drop
the final number of the year, replacing it with a zero. We can do that by
dividing the year by 10, truncating the value to its integer portion, then
multiplying by 10. For instance, take the year 2015. If we divide by 10, we get
201.5. We then truncate to the integer portion, giving us 201. Finally, we
multiply by 10 to get 2010. Century works in exactly the same way except we
divide and multiply by 100. And, if you ever needed it, you could do the same
thing for millennium using 1000.

__Decade__

// Truncate to the decade.

**INT****(****YEAR****(****[Date]****)/10)*10**

__Century__

// Truncate to the century.

**INT****(****YEAR****(****[Date]****)/100)*100**

Next, let’s talk about getting the last of a specific day in
the month. For example, I might want to get the last Sunday of the month. This
is a bit tricky, so to keep things as understandable as possible, we’ll create one
helper calc that gets the weekday of the last day of the month (we’re using a
previously-created calc for this).

__Last Day - Helper Calc 1__

// Weekday of the last day of
the month.

**DATEPART****(****'weekday'****, ****[Month - Current - Last Day]****)**

Rather than 7 separate calculated fields for each day of the
week, I’ve created a parameter called **Day of Week** which will contain the
numeric day of the week (Sunday = 1, Monday = 2, etc.). You can use this to
specify which day of the week you need (or you can eliminate it and hard-code
it into the calc). The calculated field will start with the last day of the
month then step backward until it reaches the day you’ve specified.

__Last Specific Day of Month__

// We'll start with
the last day of the month.

// Then we'll add
days to get to the specified day of the week.

**[Month - Current - Last Day]**

**-**

**IF ****[Last Day - Helper Calc 1]**** = ****[Day of Week]**** THEN**

// First day is the right weekday, so no
need to add days**.**

** 0**

**ELSEIF ****[Day of Week] ****> ****[Last Day -
Helper Calc 1]**** THEN**

// The day we need is after the first day.

** 7-****[Day
of Week]****+****[Last Day - Helper Calc 1]**

**ELSE**

// The day we need is before the first day.

** ****[Last Day -
Helper Calc 1]****-****[Day of Week]**

**END**

Finally, my most complex calculated field will find the *nth*
of a specific day in the month. For example, you could use this to get the third
Wednesday of the month. The logic of this calculated field is similar to the above, but has to account for the instance of the day you wish to return (1st, 2nd, 3rd, 4th, or 5th). In the same way as I used Day of Week to parameterize the Last Day calculated field, I’ve created another parameter, *Day Instance* which will specify the instance of that day you need (I’ll also be using *Day of Week* again).

The logic of this calculated field is a bit more complicated, so
we’re going to build 2 helper calcs. We’ll start by getting the weekday of the
first day of the month.

__ __

__Nth Day - Helper Calc 1__

// Weekday of the first day of
the month.

**DATEPART****(****'weekday'****, ****[Month - Current - First Day]****)**

Next, we’ll create a calculated field with similar logic as the Last
Day calc. However, there are two primary differences. First, we’re always
starting with the first day of the month then counting forward. And, second, we’re
accounting for the instance number in our math (essentially, adding multiples
of 7 days, as needed).

__Nth Day - Helper Calc 2__

// We'll start with
the first day of the month.

// Then we'll add
days to get to the specified day of the week.

// Then we'll add
extra chunks of 7 days to get to the specified week/instance.

**[Month - Current - First Day]**

**+**

**IF ****[Nth Day - Helper Calc 1] ****= ****[Day
of Week] ****THEN**

// First day is the right weekday, so no
need to add** ****days.**

** 0**

**ELSEIF ****[Day of Week] ****> ****[Nth Day -
Helper Calc 1] ****THEN**

// The day we need is after the first day.

** ****[Day of
Week]****-****[Nth Day - Helper Calc 1]**

**ELSE**

// The day we need is before the first day.

** 7-****[Nth
Day - Helper Calc 1]****+****[Day of Week]**

**END**

**+**

**7*(****[Day Instance]****-1)**

But we’re not quite done yet. In some months, a given day of
the week could have five instances, while others will only have four. Thus, we
need to deal with a situation where you’ve chosen the fifth instance, but it
doesn’t exist. To address this, our final calculated field will check to make
sure that the day is in the same month as the first day of the month. If not,
then it’s an invalid date and will return NULL.

** Nth Specific
Day of Month**

// If the 5th week was chosen, the nth day could be in the next
month.

// If this is the case, make the value null.

**IF ****DATETRUNC****(****'month'****, ****[Nth Day -
Helper Calc 2]****) <> ****DATETRUNC****(****'month'****, ****[Month -
Current - First Day]****) THEN**

** NULL**

**ELSE**

** ****[Nth Day -
Helper Calc 2]**

**END**

One final note on the Last and Nth day calculations. Both of
these are only looking at the current month. However, if you need them to look
at the previous or next month, you can simply swap out the calculated field referenced.
For instance, if we wanted the previous month for our Nth day calc, we’d swap
out [Month - Current - First Day] for [Month - Previous - First Day] in each of
the three calculated fields.

**The Workbook**

I want to make it very easy for you to use these
calculations when you need them, so I’ve created a workbook
that contains all of them.

The workbook has a table listing all of the calculated fields
available. You can use the filters to choose specifically what you’re looking
for, then the table will show you the name of the calculated field as well as
the folder they are in. You can then simply download the workbook, copy the
calculated field (and any dependencies),
then paste them into your workbook.

To make
sure the calc is doing what you need, you can use the **Example Date**
parameter. After entering a date, each row in the table will show the return
value of the calculated field. For the Last Day calc, you’ll also need to enter
a value in the **Day of Week** parameter. And, for the Nth Day calc, you’ll
need to specify both **Day of Week** and **Instance Number**.

*Note: All the calculated fields are well-commented so that
you can easily follow what they are doing and why.*

Thanks so much for reading! I hope you
find this cheat sheet helpful and that it saves you some time when you’re working
with dates. If you have any other favorite date calculations, feel free to
share them in the comments.

*Header image by Starline (Freepik.com)*

Ken Flerlage, April 12, 2021

Twitter | LinkedIn | GitHub | Tableau Public

Thanks so much for this!

ReplyDeleteMy pleasure!

DeleteAwesome! I was just getting ready to start a similar file for myself. (We've certainly done it with other code)

ReplyDeletePriceless cheat sheet. Hope you can expand it to include other calculated fields you use more frequently

ReplyDelete