# of days in a month based and start and end dates

[david gabra]

Hi

I have the start and end dates and from I have Jan 16 to Dec 18 .

I need to have a formula letting me know number of days per month based on the start and end date.

thanks

Davis.

 

[vletm]

david gabra
You could select from this link:
https://chandoo.org/forum/threads/days-count-as-per-month.40179/

 

[david gabra]

perfect - thanks

 

[Peter Bartholomew]

Just in case anyone fancies getting to the bottom of this formula.
I normally prefer the array version but this makes a change!

 

[Peter Bartholomew]

… a version in which each row is a CSE array formula

 

[Peter Bartholomew]

@david gabra

I think I have managed to go one step further in producing an 'elegant' array formula solution, built within the named formula environment. The formula for Period gives a two row array from 00:00:00 on the first day of each month to 23:59:59 on the last day, i.e. the first day of the following month.

The active part of each month is bounded by the Start/End period. According to the order of dates determined by case the time period of interest is bounded by different dates, with both start and end dates given by the CHOOSE function.

MMULT is used to subtract the row of start dates from the end dates. As a check, I have added the 12 days of (last year's) Christmas!

One day I might be able to do all this with dynamic array functionality rather than relying so heavily on the array processing capability of named formulas.

Peter

@Lori Are you still around? I miss your input.

 

[david gabra]

very cool