12 Replies Latest reply on Nov 11, 2013 12:40 PM by Dallin Crump

Date Format Question

Hello,

I believe i have a softball question for someone. I know how to do this in excel but not quite in tableau yet.

I have two columns -

1. Date - "11/11/13"
2. Time (24hr as an integer representing hours) - "0", "1", "2", etc.

Can anyone assist with concatenating AND turning it into 12hr format

Desired outcome - "11/11/13 1:00am", "11/11/13 2:00am"

Chris

• 1. Re: Date Format Question

Does this get you there?

http://kb.tableausoftware.com/articles/knowledgebase/combining-date-and-time-single-field

Per the article:

DATETIME(STR([Date]) + " "
+ STR(DATEPART('hour',[Start]))+ ":"
+ STR(DATEPART('minute',[Start]))+ ":"
+ STR(DATEPART('second',[Start])))

Note that if the fields are already strings, you can eliminate the STR portions of the calc.

• 2. Re: Date Format Question

This does not work because our "Hour" -- equivalent to [Start] in your example -- is an integer.

1 of 1 people found this helpful
• 3. Re: Date Format Question

How about creating a copy of your packaged workbook file, removing all fields except for the date & time fields, and letting us play around with it?  I could keep making suggestions, but it would be a lot easier to experiment.

Someone else may have another suggestion.  Can you not convert the integer to a date or string in Tableau?

1 of 1 people found this helpful
• 4. Re: Re: Date Format Question

Thanks for the help! An example is attached. We need the date and hour dimensions to be combined into one "date time."

1 of 1 people found this helpful
• 5. Re: Date Format Question

Chris,

First, I created the "TimeCalc" calculated field to translate all the time values into times (in string format).

IF [Time] = 0 THEN "12:00am"

ELSEIF [Time]= 1 THEN "1:00am"

ELSEIF [Time]= 2 THEN "2:00am"

ELSEIF [Time]= 3 THEN "3:00am"

ELSEIF [Time]= 4 THEN "4:00am"

ELSEIF [Time]= 5 THEN "5:00am"

ELSEIF [Time]= 6 THEN "6:00am"

ELSEIF [Time]= 7 THEN "7:00am"

ELSEIF [Time]= 8 THEN "8:00am"

ELSEIF [Time]= 9 THEN "9:00am"

ELSEIF [Time]= 10 THEN "10:00am"

ELSEIF [Time]= 11 THEN "11:00am"

ELSEIF [Time]= 12 THEN "12:00pm"

ELSEIF [Time]= 13 THEN "1:00pm"

ELSEIF [Time]= 14 THEN "2:00pm"

ELSEIF [Time]= 15 THEN "3:00pm"

ELSEIF [Time]= 16 THEN "4:00pm"

ELSEIF [Time]= 17 THEN "5:00pm"

ELSEIF [Time]= 18 THEN "6:00pm"

ELSEIF [Time]= 19 THEN "7:00pm"

ELSEIF [Time]= 20 THEN "8:00pm"

ELSEIF [Time]= 21 THEN "9:00pm"

ELSEIF [Time]= 22 THEN "10:00pm"

ELSEIF [Time]= 23 THEN "11:00pm"

END

Then I created that "DateTime" calculated field to parse out the date and concatenate it with the "TimeCac" field.

STR(DATEPART('month',[Date])) + "/" + STR(DATEPART('day',[Date])) + "/" + STR(DATEPART('year',[Date])) + " " +  [TimeCalc]

The output is a string field, but it at least looks like your desired outcome.

Best,

Dallin

1 of 1 people found this helpful
• 6. Re: Date Format Question

Hi Chris

Try this formula:

STR([Date]) +" "+ IIF([Time] <=12, STR([Time])+":00am", STR(24-[Time])+":00pm")

I hope this helps

best,

Ramon

1 of 1 people found this helpful
• 7. Re: Date Format Question

Okay I just figured out how to actually get the output into DateTime format. I had to tweak my "TimeCalc" CF and add another one, but it works!

The "DateTime" CF is your desired output. See attached.

Dallin

• 8. Re: Re: Date Format Question

It appears that help has arrived in another form--let us know if you have what you need.

• 9. Re: Re: Date Format Question

Hi all -- thanks for the suggestions, but we need to be able to trend the data by date-time, therefore cannot have a string output. Ramon Martinez Dallin Crump

• 10. Re: Re: Date Format Question

Allison,

I was able to get it in DateTime format. Please see my previous post.

Dallin

1 of 1 people found this helpful
• 11. Re: Date Format Question

Thanks, everyone. Allison and I appreciate the prompt support and creative ideas.

Chris

• 12. Re: Re: Date Format Question

Great! And I was able to condense it into just one CF!

DATETIME(STR([Date]) + " " + (IF [Time] = 0 THEN "12:00:00"

ELSEIF [Time]= 1 THEN "1:00:00"

ELSEIF [Time]= 2 THEN "2:00:00"

ELSEIF [Time]= 3 THEN "3:00:00"

ELSEIF [Time]= 4 THEN "4:00:00"

ELSEIF [Time]= 5 THEN "5:00:00"

ELSEIF [Time]= 6 THEN "6:00:00"

ELSEIF [Time]= 7 THEN "7:00:00"

ELSEIF [Time]= 8 THEN "8:00:00"

ELSEIF [Time]= 9 THEN "9:00:00"

ELSEIF [Time]= 10 THEN "10:00:00"

ELSEIF [Time]= 11 THEN "11:00:00"

ELSEIF [Time]= 12 THEN "12:00:00"

ELSEIF [Time]= 13 THEN "13:00:00"

ELSEIF [Time]= 14 THEN "14:00:00"

ELSEIF [Time]= 15 THEN "15:00:00"

ELSEIF [Time]= 16 THEN "16:00:00"

ELSEIF [Time]= 17 THEN "17:00:00"

ELSEIF [Time]= 18 THEN "18:00:00"

ELSEIF [Time]= 19 THEN "19:00:00"

ELSEIF [Time]= 20 THEN "20:00:00"

ELSEIF [Time]= 21 THEN "21:00:00"

ELSEIF [Time]= 22 THEN "22:00:00"

ELSEIF [Time]= 23 THEN "23:00:00"

END))