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.
-
Never mind. Figured it out
Reply -
Glad to hear you sorted it! Would you be willing to share your solution here out of interest?
Reply -
Vicki Filaski hello Vicki. I’d be curious to see the answer. Perhaps can one day leverage for some something similar like months in a fiscal or quarter.
Reply -
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 -
Vicki Filaski Thank you kindly. Have a great rest of week!!!
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 -
You could try something like this as well:
diff_days ( start_of_month (add_days ( start_of_month ( cal_date ), 32)), start_of_month(cal_date ) )
Stuart
Reply -
Stuart Herd You have to add min() or it calculates from the beginning of the date. Good though. I'm all for less typing/simplified formulas.
min(diff_days ( start_of_month (add_days ( start_of_month (cal_date ), 32)), start_of_month( cal_date) ) )
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
