3 Replies Latest reply on Jan 23, 2017 6:44 AM by Norbert Maijoor

# Dynamically Set to Last Day of Each Month

Hi All,

I am currently using this calculation to get the average number of days between the Open Date to date.

AVG(DATEDIFF('day', [Open Date], TODAY()))

This shows an aggregation of all months to date. However instead I need to dynamically compare [Open Date] to the last day of each month. I should then get a monthly breakdown in my result e.g:

As at (Month/Year)      | Avg number of days

January 2017                 | 25

February 2017               | 39

Mars 2017                      | 31

April 2017                       | 15

May 2017                       | 12

etc..

Hope it make sense.

Would be grateful for any ideas.

Thanks

• ###### 1. Re: Dynamically Set to Last Day of Each Month

Hi Sina,

Find my approach as reference below and stored in attached workbook version 9.3

• ###### 2. Re: Dynamically Set to Last Day of Each Month

Nice solution Norbert!

Take the month part of the date -> create a date which is the 1st of the next month -> take one day away...nifty!!

• ###### 3. Re: Dynamically Set to Last Day of Each Month

Simon,

my source;)