5 Replies Latest reply on Nov 8, 2015 2:09 PM by Prayson Wilfred Daniel

# Calculate DATE field from integer month, day, year

I've got individual integer fields of month, day and year.

I want to use these fields collectively to calculate a DATE field.

I tried to use below formula for creating a DATE field:

DATE(STR([month])+"/"+STR([day])+"/"+STR([year]))

But resulting with null's.

I checked for STR([month]) results months and similarly for the others as well.. But when used collectively in the above formula not getting desired results.

• ###### 1. Re: Calculate DATE field from integer month, day, year

Perhaps you have an issue with your local settings - you're trying to create a US style date but maybe your computer is set to European style dates?

Why don't you try the more generic: str(Year) + "-" + str([Month]) + "-" + str(Day)?

You can also create another calc field to check if it's a date - ISDATE(CalcDate). This will equal true if Tableau recognises it as a date.

Andrew

• ###### 2. Re: Calculate DATE field from integer month, day, year

Post 4 or 5 rows of your date data, and then it will be easier to help.

• ###### 3. Re: Calculate DATE field from integer month, day, year

Thanks Andrew...

Tried European style and works like a charm...

Is there any way to change this and set default to US style?

1 of 1 people found this helpful
• ###### 4. Re: Calculate DATE field from integer month, day, year

I'm not 100% sure and I think it depends on which version of Tableau you're using as well. If using 6.1 this link could help: http://www.tableausoftware.com/about/blog/2011/07/tableau-localized-globalized

1 of 1 people found this helpful
• ###### 5. Re: Calculate DATE field from integer month, day, year

Thank you so much. I was working with "/" with NULLS, I even convert all to string, then danced it with

IF LEFT([StrCalendar],2)="/" AND LEFT([StrCalendar],4)="/" Then DATE(LEFT([StrCalendar], 1) + "/" + MID([StrCalendar], 3, 1) + "/" + RIGHT([StrCalendar], 4))  //e.g. 1/1/1900

ELSEIF LEFT([StrCalendar],2)="/" AND LEFT([StrCalendar],5)="/" Then DATE(LEFT([StrCalendar], 1) + "/" + MID([StrCalendar], 3, 2) + "/" + RIGHT([StrCalendar], 4)) //e.g. 1/10/1900

ELSEIF LEFT([StrCalendar],3)="/" AND LEFT([StrCalendar],5)="/" Then DATE(LEFT([StrCalendar], 2) + "/" + MID([StrCalendar], 4, 1) + "/" + RIGHT([StrCalendar], 4))  //e.g. 10/1/1900

ELSE DATE(LEFT([StrCalendar], 2) + "/" + MID([StrCalendar], 4, 2) + "/" + RIGHT([StrCalendar], 4)) // e.g. 10/10/1900

END)

just to know that the issue is "/". Wow thank you so much@Andrew Watson