Date functions in Tableau

Tableau has calculated fields which helps us apply logical or arithmetic operations for any field present in the data source

Most of the data sources use time references in it. Hence, they contain one or more date values in them. The date functions play a major role here. As the name says, these are used to perform operations on fields of data type date or date time. They are also used to create a date field or check whether it is the one. There are multiple date functions in Tableau and are used for multiple purposes. Let us see them in detail.

You can also get to see all the date functions and their basic definition from the tableau calculated field itself.
– Click on the small downward arrow as in the below pic.

Select ‘Create calculated field’ from the list
A calculated field will get opened as shown in the below pic and then click on rightward arrow over there on the right side of that calculated field.
Then, a section with all the functions will get opened as in the below pic. Either click on the downward arrow beside All and choose Date from there or click on the search bar and enter date over there.
In either ways, you will be able to see all the date functions and their basic definitions.

Before moving into the date functions, it is important to know all the date parts available in Tableau and their values.

Date Parts

• Second: 0 – 60
• Year: Value should be in four digits (ex: 2024)
• Minute: 0 – 59
• Quarter: 1 – 4
• Hour: 0 – 23
• Month: 1 – 12 or Name( January, February, etc )
• Day: 1 – 31
• DayofYear: 1 – 365
• Week: 1 – 52
• Weekday: 1 – 7 or Name( Monday, Tuesday, etc )

Date Functions

This function is used to either convert a datetime field into date data type or convert anything in general as a date. Make sure to use quotes in order to give any specific date.

• DATE(expression)
• Ex : DATE(“18-12-2023 14:35”) = 18-12-2023
• DATE(NOW()) = 18-12-2023 (Now() is a function which returns the exact date and time when executed. Assuming now() gives the value 18-12-2023 14:35:45)

Dateadd

This function is used to add or subtract certain period from any specific date. Mention the date part where you want it to be effected, interval of how much period you want to increment or decrement and date in their respective positions. The output here will be a datetime data type.

• DATEADD(date_part, interval, date)
• Ex: DATEADD(‘day’, 5, #08-04-2023#) = 13-04-2023 00:00:00
• DATEADD(‘month’, -2, NOW()) = 18-10-2023 14:35:45( Assuming the now() value as 18-12-2023 14:35:45)

Datediff

This function shows the difference between start date and end date specified.( start date is subtracted from the end date). It subtracts the specific date part mentioned and provides the output. Start of week is where you have to specify the starting week day( be it Sunday, Monday or any week day) and this is optional. If nothing is specified, it takes the value according to the data source used.)

• DATEDIFF(date_part, start_date, end_date, [start_of_week])
• Ex: DATEDIFF(“day”,#18-12-2023#,#2023-12-23 23:12:54#) = 5
• DATEDIFF(“year”, #18-12-2023#,#2021-12-23 23:12:54#) = -2

Datename

This function provides the name of the specific date part mentioned. It is useful in case of months as it provides the complete name of the month. For other date parts, it acts similar to the datepart function.

• DATENAME(date_part, date, [start_of_week])
• Ex: DATENAME(“minute”,#2023-12-23 23:12:54#) = 12
• DATENAME(“month”,#2023-12-23 23:12:54#) = December

Dateparse

This function is used to to convert any given string or string field to date time format. But you have to be sure of which format that field is in and you have to specify it exactly for it to work fine.

• DATEPARSE(format, string)
• Ex: DATEPARSE( ‘dd.mm.yy’,”23.12.21″)= 23-12-2021 00:00:00
• DATEPARSE(“dd/mm/yy hh:mm:ssaa”,”23/12/2023 11:12:12am”) = 23-12-2023 11:12:12

Datepart

This function returns any specified part of the date mentioned or date field provided. Again, start of week is optional. It would consider it according to the default data source if not provided any.

• DATEPART(date_part, date, [start_of_week])
• Ex: DATEPART(‘month’, #08-04-2023#) = 4
• DATEPART(‘dayofyear’,#28-12-2023#) = 362

Datetime

This function gives output as datetime even if we enter a number, string or date expression inside the quotation marks.

• DATETIME(expression)
• Ex: DATETIME(“December 23, 2023”)= 23-12-2023 00:00:00
• DATETIME(“12 23,2023, 12:23:13”) = 23-12-2023 12:23:13

Datetrunc

This function truncates the date part in the specified date i.e it gives the starting date based on the date part mentioned. Start of week is optional. If not mentioned any, it assigns according to the default data source.

• DATETRUNC(date_part, date, [start_of_week])
• Ex: DATETRUNC(‘month’,#28-12-2023#) = 01-12-2023 00:00:00( Output is the starting date of the date part month as mentioned.)
• DATETRUNC(‘week’, #28-12-2023#, ‘monday’) = 18-12-2023 00:00:00( If we mention tuesday as start of week, it would give 19-12-2023 as it considers that date as the start of the week)

Isdate

• This functions whether the given string is a date or not.
• ISDATE(string)
• Ex: ISDATE(“28,12, 2023”) = True
• ISDATE(“28-12-202323:22:45”) = False

Makedate

• This returns a date as output based on the inputs given in their respective places.
• MAKEDATE(year, month, day)
• Ex: MAKEDATE(2023,04,08) = 08-04-2023
• MAKEDATE(20,04,08) = No output ( As it doesn’t recognize the year in it’s respective place.)

Makedatetime

This function returns date time as out put based on the date and time mentioned inside hashtags.

• MAKEDATETIME(date, time)
• Ex: MAKEDATETIME(#12-23-2023#,#12:23:34#) = 23-12-2023 12:23:34
• MAKEDATETIME(today(), #12:23:34#) = 23-12-2023 12:23:34 ( Today() is a function which provides the today’s date, considering today as 23rd of December 2023)