gah.

Why is some of this stuff so challenging in Tableau?  You have a date and you want to format it as a string? Why? So you can concatenate it with another string perhaps? Maybe you’re creating a calculated field to use in a label (that’s what I was doing).

Unfortunately, when you convert a datetime to a string in Tableau it uses the format yyyy-mm-dd hh:mm:ss. Fine for a lot of cases, not fine for a lot of cases.

Being the purveyor of fine calculated measures I created this wonderful calc to convert a datetime to a string in the format: mm/dd/yyyy hh:mm AM/PM. Are you looking for the same thing with time removed (mm/dd/yyyy)? No problem! That’s here too!

If you want your string date in the format mm/dd/yyyy hh:mm AM/PM create a calculated field as follows:

//Format date as string mm/dd/yyy
str(datepart('month',[Power Reconnect Date Time]))+'/'
+str(datepart('day',[Power Reconnect Date Time]))+'/'
+str(datepart('year',[Power Reconnect Date Time]))
//add hh:mm AM/PM 
 //build hour (special logic to show 12 hour time instead of 24 hour)
 +' '
 + if datepart('hour',[Power Reconnect Date Time]) > 12 
 then str(datepart('hour',[Power Reconnect Date Time])-12) //After 12pm
 ELSEIF datepart('hour',[Power Reconnect Date Time]) = 0 //12am (midnight)
 then '12'
 ELSE str(datepart('hour',[Power Reconnect Date Time])) // 1am to 12pm
 END
 //build minute
 +':'
 + if datepart('minute',[Power Reconnect Date Time])<10
 then '0'+str(datepart('minute',[Power Reconnect Date Time]))
 ELSE str(datepart('minute',[Power Reconnect Date Time]))
 END
 //add AM/PM
 +' '
 +if datepart('hour',[Power Reconnect Date Time]) >= 12 
 then 'PM'
 else 'AM'
 END

Do you only want mm/dd/yyyy? No problema! The same formula with the last section removed will do it:

//Format date as string mm/dd/yyy
str(datepart('month',[Your Date Time]))+'/'
+str(datepart('day',[Your Date Time]))+'/'
+str(datepart('year',[Your Date Time]))

Got another format you’re looking for? Ask and you shall receive! Post a comment and I’ll add it to the article. Together we can make Tableau a better place. 😉