Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Friends
I need to count number of days in a selected months. It seem easy but there is some condition.
I have two different filters Year and Month
Suppose I selected year=2019 so the result should be, Number of days from 1 Jan 2019 till Today.
Suppose I selected year=2019 and Month=Jan, So the result should be 31
Suppose I selected year=2019 and Month=Jan and Feb, So the result should be 31+28=59
Suppose I selected year=2019 and Month=Mar and Apr, So the result should be 31+16=47
Suppose I selected year=2019 and Month=Apr, So the result should be 16
Regards
Shahzad
Hi,
Note: Month1 = value 1 to 12 in the selection pane.
If(Index(GetFieldSelections(Month1),1)>0,31,0)+
If(Index(GetFieldSelections(Month1),2)>0,28+If(Fmod(Max(GetFieldSelections(Year1)),4)>0,1,0),0)+
If(Index(GetFieldSelections(Month1),3)>0,31,0)+
If(Index(GetFieldSelections(Month1),4)>0,30,0)+
If(Index(GetFieldSelections(Month1),5)>0,31,0)+
If(Index(GetFieldSelections(Month1),6)>0,30,0)+
If(Index(GetFieldSelections(Month1),7)>0,31,0)+
If(Index(GetFieldSelections(Month1),8)>0,31,0)+
If(Index(GetFieldSelections(Month1),9)>0,30,0)+
If(Index(GetFieldSelections(Month1),10)>0,31,0)+
If(Index(GetFieldSelections(Month1),11)>0,30,0)+
If(Index(GetFieldSelections(Month1),12)>0,31,0)
+
If(Index(GetFieldSelections(Month1),Num(Month(Today())))>0,
(Today()-MonthStart(Today())+1)-
(MonthEnd(Today())-MonthStart(Today()))
,0)
Month1 is one of my field in my database, which have value 1 to 12 which represents the month (1,2,3,4,5,6,....12). 1 = Jan.
In short, what it does is:
I have 2 filter pane that allows users to choose the Month and Year.
If users choose Month 1 and 4, which is Jan and April, it will be 31 days + 16 days as in your example.
The index formula is first looking at what month the users select and return the total of days in a month.
EG: At the Index formula, it detects 4 (April) is selected, so, = 30 day.
And then If the selected month = today's month, it will minus the total days of the month (30 days) and get the number of days of the month (16 days 1st April to 16 April using your example).