In the following examples, I will explain how the different date functions work in Tableau. I am using a specific date example, so I am wrapping it in hashtags: #2023-04-15#. If you wanted to apply these functions to one of your data fields, simply swap out the #2023-04-15# for the name of your data field: [Data Field Name].
The layout is as follows:
>The input date into the function
>THE FUNCTION()
>The Output
THE ESSENTIALS
1. DATEADD.
Literally adding time to the date in question.
>2023-04-15
>DATEADD(‘month’, ‘3’, #2023-04-15#)
>2023-07-15
Specify which part of the date you want to add to: ‘year’, ‘month’ ‘day’ (be careful, they are case specific). By specifying ‘month’ and ‘3’, we have added 3 months to the initial date.
2. DATEDIFF.
The difference between two dates.
>2023-04-15 – 2023-04-10
>DATEDIFF(‘day’, #2023-04-10#, #2023-04-15#)
>5
The later date needs to be the second date you include, because the start date is subtracted from the end date. You need to specify what unit of difference you need to make: is it ‘days’, ‘months’ or ‘years’?
3. DATENAME.
You can return a part of the date that you feed the function as a string (in word form).
>#2023-04-15#
>DATENAME(‘month’, #2023-04-15#)
>April
In your function, you just need to specify what part of the date you want returned as a date (day/month/year).
4. DATEPART.
Just like DATENAME but it will return a number not a string.
>#2023-04-15#
>DATEPART(‘month’, #2023-04-15#)
>4
5. DATETRUNC.
Can truncate your date to the point you want, and will standardize the rest of the date from that point.
Points of truncation: Year – Quarter – Month – Day – Hour – Minute – Second
>#2023-04-15#
>DATETRUNC(‘month’, #2023-04-15#)
>2023-04-01 00:00:00
It helps to know what the standardized/default values become so you can se where you have truncated your date. Year (1980) and day (01) are a good start.
CHANGING DATA TYPES
6. DATEPARSE.
Change the data type from a string à date. You might have merged columns to combine the year and month for example, but you need to tell tableau to recognize that combo as a date type.
>#2023.April.15#
>DATEPARSE(‘yyyy.MMMM.dd’, #2023-April-15#)
>2023-04-15
You need to tell Tableau the exact breakdown of the date you have in your data field...
‘yyyy’ = 2023 / ‘yy’= 23
‘MM’= 04 / ‘MMMM’= April
‘dd’= 15
7. DAY.
Change the data type from date to an integer
DAY(#2023-04-15#) --> 15
8. ISDATE.
You are asking if your string data field can be read as a date. It will return a True/False. Would be good to use before you do a DATEPARSE function.
ISDATE(#2023-04-15#) --> TRUE
ISO
ISO means the ISO8601 date code. It will return the part of the date you want in the ISO8601 format…
ISOWEEK(#2023-04-15#) --> 15
ISOWEEKDAY(#2023-04-15) --> 1 This means Monday.
Rinse and repeat for:
ISOQUARTER
ISOYEAR
MAX/MIN
MAX.
Tells you the max between two arguments. Doesn’t just have to be used on dates.
MAX(#2023-04-15#, #2023-04-10#) --> #2023-04-15#
MIN.
The opposite of the MAX function.
THE FIND/TELL BUTTON FOR DATES
MONTH(#2023-04-15#) --> 4
QUARTER(#2023-04-15#) --> 2
Rinse and repeat…
WEEK()
YEAR()
TODAY() --> 2023-10-18 (today’s date)
NOW() --> 2023-10-18 6:34:45 PM (the exact time you apply the function)