how many days in each month of date range?

**mrwm** · 06-24-2013, 01:31 PM

I'm trying to calculate how many days fall into each month from a date range.

As example: Date Range: Jan 15, 2013 - April 3, 2013

I need formula that calculates the total days and fills in how many days per month as shown below:

15-Jan-13 3-Apr-13

jan feb mar apr may june july aug sept oct nov dec
16 28 31 3 0 0 0 0 0 0 0 0

Can you help me? Thanks!!

**dilipandey** · 06-24-2013, 01:44 PM

Hi mrwm,

what would be the results if your end date is Jan 20, 2014 ?

Regards,
DILIPandey
<click on below * if this helps>

**mrwm** · 06-24-2013, 01:52 PM

thanks for your help. I only need to calculate within a date range that will always be within one calendar year Jan - Dec.

**dilipandey** · 06-24-2013, 02:16 PM

HI mrwm

Use below formula:-

Formula:

Please Login or Register  to view this content.

days in month.xlsx

Regards,
DILIPandey
<click on below * if this helps>

**mrwm** · 06-24-2013, 03:05 PM

Awesome, thank you DiliPandey!!

**dilipandey** · 06-24-2013, 03:08 PM

You are welcome. . .

Cheers

Regards,
DILIPandey
<click on below * if this helps>

**mrwm** · 06-24-2013, 03:57 PM

DiliPandey,

I need a bit more help if you don't mind... I have the formula working in the attached if month starts in January, but it is not working if it starts in a month past January. Can you help me fix it?

**dilipandey** · 06-25-2013, 04:00 AM

Okay.. see the attached file:-

new formula.xlsx

Regards,
DILIPandey
<click on below * if this helps>

**mrwm** · 06-26-2013, 08:39 AM

Awesome! Thank you so much!!

**dilipandey** · 06-26-2013, 08:42 AM

you are welcome...

Regards,
DILIPandey
<click on below * if this helps>

**daddylonglegs** · 06-26-2013, 09:24 AM

You could also use this formula in D2 copied across and down

=MAX(0,MIN($C2,EOMONTH(D$1,0))+1-MAX($B2+1,D$1))

if you don't want to see zeroes custom format as

General;;

how many days in each month of date range?

LinkBack

Thread Tools

Rate This Thread

Display

how many days in each month of date range?

Re: how many days in each month of date range?

Re: how many days in each month of date range?

Re: how many days in each month of date range?

Re: how many days in each month of date range?

Re: how many days in each month of date range?

Re: how many days in each month of date range?

Re: how many days in each month of date range?

Re: how many days in each month of date range?

Re: how many days in each month of date range?

Re: how many days in each month of date range?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions