9 Replies Latest reply on Aug 20, 2014 8:50 AM by Xiaojun Wu

# How to create a date field with numbers

Hi,

I have a question. How could I careate a calculated field with two fields(year number and month number)?

For example:

Field 1: 2014

Field 2: 05

I want a calcuated field 3 to show as the last day of the month: 05/31/2014

Thanks

• ###### 1. Re: How to create a date field with numbers

The DATE function will do that for you.

Getting the last day of the month ... maybe there is some function I'm missing, but I'm not sure how to tell the calc to be the last day of a given month.  If I had to do this with my limited knowledge, I would add one to the month, set the day value to 1 (so if you have 5 in your field, create 6/1) and then do a DATEADD of -1 to go back one day.  (You'll have to check if your month is 12.  Set it to 1 instead of adding 1, add 1 to the year, so that you end up with 1/1/201x for next year, then do the DATEADD of -1 to go back a day.)

Of course, instead of doing the dateadd, you could hard-code all the last-days of each month and cram that value in there.

• ###### 2. Re: How to create a date field with numbers

I have added "DAY" column for calculation purpose but you can hard code it as well (any no. from 1 to 28).

You can either create 2 calculated field or can merge the calculation in one.

See Row 1. Tableau  handles change in year by itself so no extra coding needed.

I hope this helps.

Thanks.

• ###### 3. Re: How to create a date field with numbers

A better way to get the date from month and year is:

To get the last day of the current month, you can add a day, truncate to month, and subtract a day:

Dan

1 of 1 people found this helpful
• ###### 4. Re: How to create a date field with numbers

Thanks and based on your recommendation:

last day of the month

One more quesiton for you:

Do you know a best way to calculate Net Working Days between two dates in Tableau?

Seached and got no correct answer so far. It seems that you are good at with formula.

Thanks

• ###### 6. Re: How to create a date field with numbers

I have checked this calculation before.

April 2014 should have 22 business days and it bring up 21 business day.

• ###### 7. Re: How to create a date field with numbers

Datediff('week',[First day of the month ],[Last day of the month])*5

+

Min(datepart('weekday',[Last day of the month]),5)

-

Min(datepart('weekday',[First day of the month ]),5)

seems does not get correct net working days in July regardless of any holiday.

• ###### 8. Re: How to create a date field with numbers

See the change in formula.

Find the snapshot attached.

• ###### 9. Re: How to create a date field with numbers

does not work on some of the months

for example the fomular shows 21 in Feb 2014 it is actual 20 days excluding Weekends.