-
1. Re: how to calculate number of days in a month dynamically
Mark Fraser Apr 20, 2015 5:48 AM (in response to Siddula Navaneetha)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
Siddula Navaneetha Apr 20, 2015 5:59 AM (in response to Mark Fraser)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
Mark Fraser Apr 20, 2015 6:04 AM (in response to Siddula Navaneetha)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
Shawn Wallwork Apr 20, 2015 6:46 AM (in response to Siddula Navaneetha)2 of 2 people found this helpfulNot 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
-
5. Re: how to calculate number of days in a month dynamically
Thiago Teixeira Feb 1, 2016 1:56 PM (in response to Siddula Navaneetha)5 of 5 people found this helpfulDAY(DATETRUNC('month',DATEADD('month',1,[Order Date]))-1)
-
6. Re: how to calculate number of days in a month dynamically
Pablo Saenz de Tejada Apr 7, 2016 1:28 AM (in response to Siddula Navaneetha)12 of 12 people found this helpfulAn alternative solution could be:
DATEDIFF('day',DATETRUNC('month', [Date]),DATETRUNC('month', dateadd('month',1,[Date])))
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:
-
7. Re: how to calculate number of days in a month dynamically
Swarup K Apr 7, 2016 2:43 AM (in response to Siddula Navaneetha)DATEDIFF('day',DATETRUNC('month', [Order Date]),DATEADD('month',1,[Order Date]))
-
8. Re: how to calculate number of days in a month dynamically
Mas Agung Hamzari Jan 20, 2017 5:49 AM (in response to Siddula Navaneetha)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.
Please advice how to solve this problem...
-
Data Set.xlsx 9.5 KB
-
-
9. Re: how to calculate number of days in a month dynamically
christinadavid.pilli Apr 27, 2017 10:06 AM (in response to Mark Fraser)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
Chris Berger Jun 8, 2017 9:18 AM (in response to Thiago Teixeira)Simple and practical solution
-
11. Re: how to calculate number of days in a month dynamically
Murthy Pidugu Sep 26, 2017 7:51 PM (in response to Siddula Navaneetha)1 of 1 people found this helpfulHi,
1) No of Days left in current month?
Create 2 calculated fields like Last Day & Days Left
Last Day : DATEADD('day',-1, DATEADD('month',1, DATETRUNC('month', TODAY())))
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])
hope this may help you guys.... Thanks
-
12. Re: how to calculate number of days in a month dynamically
Kim Cataldo Dec 18, 2017 2:30 PM (in response to Pablo Saenz de Tejada)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
Richard Koehler Feb 27, 2018 9:13 AM (in response to Kim Cataldo)1 of 1 people found this helpfulKim,
Pablo's formula works for quarters or years as well if you just change the 'month' part to quarter or year.
DATEDIFF('day',DATETRUNC('month', [Date]),DATETRUNC('month', dateadd('month',1,[Date])))
The dynamic part might require a parameter instead.
Richard
-
14. Re: how to calculate number of days in a month dynamically
Daniel Klaus Apr 9, 2019 11:22 AM (in response to Siddula Navaneetha)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