Need to calculate number of days in any given month
I've tried a number of different formulas, but none are working as intended. It will be used to calculate the daily average for any given month. I'd rather have it dynamic so it accounts for leap year and other variants. It will be used in an answer, not in a worksheet, running 4.5.1.3.
-
In my case I just needed it to calculate the daily average for the current month. So the formula will calculate 30 for the current month - November. I created two formulas to nest within.
min( if ( current month = transdate_month ) then diff_days ( first day of next month , first day of month ) else 0 )
First Day of Next Month:
to_date(if (month_number ( today() ) <12) then concat (concat(concat (to_string (year(today() )),'/'), to_string (month_number ( today() )+1)),'/1' ) else concat (to_string (year(today() )),'/1/1'),'%Y/%m/%d')
First day of month:
start_of_month ( transdate )
Reply -
if ( date_month = 1 ) then 31 else if ( date_month = 3 ) then 31 else if ( date_month = 5 ) then 31 else if ( date_month = 7 ) then 31 else if ( date_month = 8 ) then 31 else if ( date_month = 10 ) then 31 else if ( date_month = 12 ) then 31 else if ( date_month = 4 ) then 30 else if ( date_month = 6 ) then 30 else if ( date_month = 9 ) then 30 else if ( date_month =11 ) then 30 else if (date_month = 2 and day_number_of_year ( transdate ) = 60) then 29 else 28
Reply -
An alternate solution which minimises formulas is to have a date table with a 1 for each day of the month. Then you can just Sum the column. suym(days_in_period). This works very well as when you change from weekly, monthly, yearly the result returned is the number of days in the period. You should use the date column from this table in your worksheet.
Reply