9 Replies Latest reply on Sep 18, 2013 12:39 PM by Alexander Severinsen

# How to get data from integer fields month and year

I´ve got two integer fields with month 1-12 and year (four digits). I am trying to format that to a date field. I tried a calculated field:

DATE(STR( [year] )+ '.1.'+STR([month]) )

That seemed to work, but then I notice that day is month and month is day. I thought that that just changing into DATE(STR( [year] )+STR([month])+ '.1.' ) would help but that messes everything up... I tried to change workbook local, but that did not help either. Any suggestions?

Thanks.

Kind regards

Alexander

• ###### 1. Re: How to get data from integer fields month and year

Try this:

DATE(STR([Month ])+"/01/" + STR(Year))

Example attached that I replicated in Excel--from here, you can change the Date format to whatever you want.

1 of 1 people found this helpful
• ###### 2. Re: How to get data from integer fields month and year

Thanks, Matthew. However, DATE(STR([month])+"/01/" + STR(year)) gives me only NULL. When I DATE(STR( [year] )+ '.1.'+STR([month]) ) i get a date field equal to 12.01.2009 (when year=2009, month=12) . But that should be 01.12.2009. I am puzzled that is going on.

• ###### 3. Re: How to get data from integer fields month and year

Alexander sorry to tell you all of your problems seem to be typos, or misunderstanding what you're writing.

Typo: DATE(STR([month])+"/01/" + STR(year))  -- You're using the (year) instead of [ year] also sometimes caps matter so it's always best to double click the field in the Fields box below left. That way it will be exactly the way T wants it.

Typo: DATE(STR( [year] )+STR([month])+ '.1.' ) -- The equation inside the DATE() will produce a string like this: 200212.1. and then when you ask T to turn that into a date it's going to give you a whacked date.

Here's the equation you seem to be looking for: DATE(STR( [year] )+ '.' + STR([month])+ '.1' ) -- this will produce a string like this: 2002.12.1 and T can turn this into a correct date.

Hope this helps,

--Shawn

1 of 1 people found this helpful
• ###### 4. Re: How to get data from integer fields month and year

Thank you for helping out, Shawn. I am sorry, that first () typo was only in the forum. The code in T was,

DATE(STR( [year] )+ '.1.'+STR([month]) )

And that does produce a date as I stated equal to 12.01.2009 (when year=2009, month=12). That is consistent through all dates.

However, when I try your suggestion DATE(STR( [year] )+ '.' + STR([month])+ '.1' ) that gives only NULL. Not sure what I might be doing wrong.

Alex

• ###### 5. Re: How to get data from integer fields month and year

You are correct. T isn't recognizing '.' as a date separator in this instance. Change it to this:

DATE(STR( [Year] )+ '/' + STR([Month ])+ '/1' )

Then go into the Date format box and format it like this:

[Somebody need slap that Shawn guy upside the head for not testing first!]

--Shawn

1 of 1 people found this helpful
• ###### 6. Re: How to get data from integer fields month and year

Thanks again, Shawn. However, I still only get NULL values. I´ve tried your

version:

DATE(STR(  )+ '/' + STR()+ '/1' )

and with dots and hyphens

DATE(STR(  )+ '.' + STR()+ '.1' )

DATE(STR(  )+ '-' + STR()+ '-1' )

I´ve also tried changing the date format box in a number of ways, yours

included. I also actually dreamt about date formats tonight, and it was not

pleasant dream!

Weird thing is that the only code that is close is DATE(STR(  )+

'.1.'+STR() ). That however mixes up months and dates. And I am

considering going back to the database and creating a new date field using

SQL. Still, would be nice to understand what I am doing wrong.

Kind regards

Alexander

2013/9/12 Shawn Wallwork <tableaucommunity@tableausoftware.com>

**

Tableau Support Community

How to get data from integer fields month and year

Forums - View the full discussion<http://community.tableau.com/message/226308?et=watches.email.thread#226308>

• ###### 7. Re: How to get data from integer fields month and year

Hmm. Just a note. I replied to the e-mail, and the code looks completely different there. That is

DATE(STR( [Year] )+ '/' + STR([Month ])+ '/1' )

and with dots and hyphens

DATE(STR( [Year] )+ '.' + STR([Month ])+ '.1' )

DATE(STR( [Year] )+ '-' + STR([Month ])+ '-1' )

Seems that some formatting is going on between the mail server and this forum!?

Alex

• ###### 8. Re: How to get data from integer fields month and year

We really are going to need to see a workbook. Since we can't reproduce the results you are reporting, we can't solve your issues. Sorry,

--Shawn

• ###### 9. Re: How to get data from integer fields month and year

Sorry about the slow response. I had a friend try DATE(STR([year])+ '/' + STR([month])+ '/1' ). Worked like a charm. He sent me his workbook, and worked like a charm at my computer as well. I suspect I have some odd time-setting that might have caused the problem. Anyway, solved. Thanks!