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.

9replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Never mind. Figured it out

    Reply Like
  • Glad to hear you sorted it! Would you be willing to share your solution here out of interest?

    Reply Like
  • 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 Like
  • 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 Like
  • Vicki Filaski Thank you kindly.  Have a great rest of week!!!

    Reply Like
  • 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 Like 1
  • 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 Like 1
  • 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 Like 1
  • 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 Like
Like1 Follow
  • Status Answered
  • 1 Likes
  • 1 mth agoLast active
  • 9Replies
  • 229Views
  • 5 Following