14 Replies Latest reply on Apr 9, 2019 11:22 AM by Daniel Klaus

# how to calculate number of days in a month dynamically

Hi

May  I Know how to calculate number of days in a month dynamically.

thanks

Navanita

• ###### 1. Re: how to calculate number of days in a month dynamically

Hi Navanita

1) Do you mean number of days between 2 dates?

2) Or how many days in January, February etc.?

Assuming its 1, you could use a datediff function

datediff('day',[first_date],[second_date])

Ifits 2, you could use a calendar

Cheers

Mark

• ###### 2. Re: how to calculate number of days in a month dynamically

thanks for reply but I would like to know 2nd condition, what is that calendar date can u explain briefly

• ###### 3. Re: how to calculate number of days in a month dynamically

Hi Navanita

I said it rather tongue in cheek, apologies.

Everyone using the Gregorian calendar operates with a fixed number of days per month (apart from February in a leap year) Gregorian calendar - Wikipedia, the free encyclopedia

I'm sure this isn't your question, have missed something?!

• ###### 4. Re: how to calculate number of days in a month dynamically

Not to but in here, but I think he/her is looking for this calculation:

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

I could be wrong.

--Shawn

2 of 2 people found this helpful
• ###### 5. Re: how to calculate number of days in a month dynamically

5 of 5 people found this helpful
• ###### 6. Re: how to calculate number of days in a month dynamically

An alternative solution could be:

So you are calculating the number of days between the first day of the month and the first day of the next month. If you define the aggregation as an average, you will get something like this:

12 of 12 people found this helpful

• ###### 8. Re: how to calculate number of days in a month dynamically

Hi Friends,

I already follow the instructions above and still not works for me. In my case, i would like to convert the Sales in mmbtu per month and convert it into BBTUD. Doing in excel just divided the Sales in MMBTU with 1000 and the number of day in a certain month.

• ###### 9. Re: how to calculate number of days in a month dynamically

Hi Mark,

I am working on a projects tracking dashboard and I need to find out how many projects are ending in the next 30 days and how many are ending in 60 days..I have an actual start date,actual end date,estimated end...I am confused as to how to get the results..I needs to give me the number of projects ending in those days.

• ###### 10. Re: how to calculate number of days in a month dynamically

Simple and practical solution

• ###### 11. Re: how to calculate number of days in a month dynamically

Hi,

1) No of Days left in current month?

Create 2 calculated fields like Last Day & Days Left

Days Left : DATEDIFF('day', TODAY(),[Last Day])

2) No of Days completed in current month?

Create 1 more calculated field with Days Completed

Days Completed: DATEADD('day', DATEPART('day',TODAY()), [Last Day])

1 of 1 people found this helpful
• ###### 12. Re: how to calculate number of days in a month dynamically

Hi Pablo,

I realize this scenario is a little different than the original question, but I was wondering if I could pick your brain on how to make this formula dynamic to adjust to quarter or year, depending what is in the view?  I am looking for a formula that will calculate the number of days within the time period shown, which will change when you drill into the date hierarchy from year to quarter to month.

Thanks for considering!

• ###### 13. Re: how to calculate number of days in a month dynamically

Kim,

Pablo's formula works for quarters or years as well if you just change the 'month' part to quarter or year.

The dynamic part might require a parameter instead.

Richard

1 of 1 people found this helpful
• ###### 14. Re: how to calculate number of days in a month dynamically

I had a similar situation but didn't actually have a proper date to go off of, just a month number 1-12.  This is how I approached it:

CASE [Month Number]

WHEN 1 THEN 31

WHEN 2 THEN IF [Is Leap Year] THEN 29 ELSE 28 END

WHEN 3 THEN 31

WHEN 4 THEN 30

WHEN 5 THEN 31

WHEN 6 THEN 30

WHEN 7 THEN 31

WHEN 8 THEN 31

WHEN 9 THEN 30

WHEN 10 THEN 31

WHEN 11 THEN 30

WHEN 12 THEN 31

END

Where [Is Leap Year] is just:

[Year] % 4 = 0