Graphed LogoGraphed
Login

How to Write a Date in Tableau Calculated Field

Cody Schneider•

Working with dates in Tableau calculated fields seems straightforward until you're met with a cryptic error message. You know the date you typed is correct, but Tableau disagrees. This happens because Tableau needs dates written in a very specific format to understand them inside a calculation. This guide will show you exactly how to write dates in Tableau calculated fields and introduce the essential functions that give you control over your date-based analysis.

Understanding How Tableau Sees Dates

Before writing a formula, it's helpful to remember that Tableau categorizes your data into different types, like strings (text), integers (whole numbers), and dates. When you create a calculated field, Tableau tries to ensure the comparison makes sense. For example, it understands comparing one date to another (is January 1st before January 15th?) but gets confused if you ask it to compare a date to a plain text value like "January 1st, 2024."

This is the root of most date calculation errors. You might be typing a valid date, but you're not formatting it in the special way Tableau requires to immediately recognize it as a date within a formula. This special format is called a date literal.

The Correct Syntax: The Date Literal

To tell Tableau you're referencing a specific, fixed date within a formula, you must wrap it in hashtags (#). This is the most important rule to remember. Using standard quotes (" ") will make Tableau think it's a string, not a date, which will cause your calculation to fail.

The required format for a date literal is:

Breaking it down:

  • The formula must start and end with a hashtag (#).

  • YYYY: A four-digit year (e.g., 2024).

  • MM: A two-digit month (e.g., 01 for January, 12 for December).

  • DD: A two-digit day (e.g., 05, 15, 31).

For example, to specify the last day of 2023, you would write:

Including Time with a Datetime Literal

If your field includes timestamps and you need to be that specific, you can use a datetime literal, which follows a similar structure:

For instance, to reference 9:00 AM on May 20, 2024, you'd write:

Practical Examples of Date Literals

Let's see how this works in a real calculated field. Imagine you want to create a field that flags sales orders that happened in 2024.

Example 1: Flagging Recent Orders

You want to label any order that occurred after December 31, 2023.

Calculation:

In this formula, Tableau compares the value in your [Order Date] field to the specific date of December 31, 2023. Because we used the #...# syntax, Tableau knows it's a date-to-date comparison and can correctly evaluate it for every row.

Example 2: Filtering for a Specific Campaign Period

Suppose your marketing team ran a campaign during the entire month of April 2024 and you want to isolate sales from that period.

Calculation:

This calculated field will return the [Sales] amount for orders placed within April and return a null value for all other dates. You can then use this new field in your visualizations or drag the original [Order Date] field to the Filters shelf and set the range using these specific dates.

Working with Dynamic Dates Using Functions

Hardcoding dates is great for fixed points in time, like the start of a fiscal year or a specific event date. But often, you need to create calculations relative to the current date. For that, you use Tableau's built-in date functions.

TODAY() and NOW()

These are two of the most popular functions for dynamic analysis.

  • TODAY(): Returns the current date (e.g., 2024-05-21).

  • NOW(): Returns the current date and time (e.g., 2024-05-21 14:30:15).

You can use these to create reports that are always up-to-date. For a dashboard that shows sales from the last 7 days, you could create a calculated field to use as a filter:

Note: The double hyphen is a dash-minus sign, ensure proper spacing and dash use as per syntax.

MAKEDATE(year, month, day)

This function is incredibly useful when your year, month, and day are stored in separate fields. It constructs a valid date from three numbers.

For example, if you have [Order Year] (e.g., 2023), [Order Month] (e.g., 10), and [Order Day] (e.g., 28) as separate integer fields, you can combine them like so:

This function is also great for dynamically creating a specific date, like the first day of the current year.

Example:

Get the first day of whatever year it is now.

DATE() and DATEPARSE()

Sometimes your date information arrives as a string (text) and needs to be converted. These two functions handle that job, but they work slightly differently.

  • DATE(expression): This function attempts to convert a number, string, or date expression into a date. It's smart and can often recognize standard formats like "2024-01-15". For example:

will turn that text string into a real date.

  • DATEPARSE('format', 'string'): This function is for more complex or non-standard string formats. You provide a pattern to tell Tableau exactly how to read the string. For example, for a date field [Date String] containing "15-Jan-2024":

Common Mistakes and How to Fix Them

Avoiding a few common pitfalls will save you a lot of time debugging your calculations.

1. Using Regular Quotes Instead of Hashtags

The Mistake: [Order Date] >, "2024-01-01"

Why it Fails: Tableau sees "2024-01-01" as text. This results in a "Can't compare date and string values" error.

The Fix: Always use hashtags for date literals.

2. Using Incorrect Format Inside Hashtags

The Mistake: [Order Date] = #Jan 1, 2024# or [Order Date] = #1/1/2024#

Why it Fails: Date literals must strictly follow the YYYY-MM-DD standard.

The Fix: Write the date in the required format.

3. Comparing Dates with Different Time Levels

The Mistake: Comparing a date-only field [SignUpDate] with a datetime-literal [SignUpDate] = #2024-03-10 12:00:00#

Why it Fails: A date value is implicitly set to midnight (00:00:00). Your comparison [SignUpDate] = (March 10th at midnight) will only be true for signups that happened at that exact second. To check for any signup on that day, you need to either remove the time component from the datetime field or avoid using one in your literal.

The Fix: Use a date part function like DATETRUNC or compare to a simple date literal.

Final Thoughts

Mastering dates in Tableau comes down to remembering the syntax for date literals (#YYYY-MM-DD#) and knowing which function to use for dynamic situations. Once you internalize these rules, you'll be able to quickly filter, flag, and calculate new measures based on any time period your analysis requires.

Of course, even when you know the rules, sometimes you just need an answer without fussing over formula syntax. This is where tools for automated analysis are changing the game. With Graphed, for example, we built an AI data analyst that allows you to skip the syntax altogether. Instead of writing IF [Order Date] >= #2024-01-01# ..., you just connect your data and ask in plain English: "Show me total sales for orders after January 1st, 2024." Our platform automatically generates the dashboard, saving you from having to remember specific formats and letting you focus on the insights.