4 Replies Latest reply on Jun 27, 2016 7:59 PM by Rody Zakovich

# days in a month calculation

Good day,

at moment I try to do a working days calculation and for this I have started to calculate the days of a month first. I found a lot of solutions in the net but for any reason I am not able to reproduce them as I am always getting nuts with the filter I need to set.

As an example I have add a twbx where I hopefully can demonstrate the problem I have.

I have create a simple excel spreadsheet with only two coloumns: working day and country.

the reason why is very simple. on some days we only work in country 1 but on other days we work in country 2. But for the days of a month calculation I always need all days and not only the days in the country. So what I need is at the end:

It doesn´t matter what filter is set the calculation must always be the same. So Jan for example always have 31 days and it doesn´t matter which filter is set.

I really hope you can help me

• ###### 1. Re: days in a month calculation

I hope this helps.

Calculate 1st date of month.

[Working day YM1]

date(str(year([Working day]))+","+str(month([Working day]))+",1")

Calculate the days of month with taking difference of 1st date of NEXT montn.

[Day in Month]

datediff('day',[Working day YM1],dateadd('month',1,[Working day YM1]))

Change the field to discrete.

Thanks,

Shin

9.3 attached

1 of 1 people found this helpful
• ###### 2. Re: days in a month calculation

Hi,

wow this rocks. It is perfectly working. Thanks a lot

• ###### 3. Re: days in a month calculation

Sounds good.

You are very welcome!!

Shin

• ###### 4. Re: days in a month calculation

Just adding this in for reference (assuming I am understanding the question)

DATEPART('day', DATEADD('day', -1, DATEADD('month', 1, DATETRUNC('month', [DATE]))))

1. Truncate to the Month Level (The 1st of the Month - June 1st)

2. Add a Month (Now it is July 1st)

3. Subtract a day (Now it is June 30th)

4. Get the Datepart of day (30)

Hope this helps!

Regards,

Rody

1 of 1 people found this helpful