Excel - count days in each month between two dates both dates available

0 votes

I have found the solution thought it would be good to share:

so the formula should be : =((MAX(CT$6-$N8,0)-MAX(EOMONTH(CT$6,-1)-$N8,0))-(MAX(CT$6-$O8,0)-MAX(EOMONTH(CT$6,-1)-$O8,0))+(EOMONTH(CT$6,0)=EOMONTH($N8,0)))*1

:)

I am trying to write an excel formula which can be dragged across a row of cells to give the number of days in each month between two specified dates. E.g:

enter image description here

I tried using a if with sum product formula from the same website but for some reason it is not recognising the days of last month. was wondering if someone could help explain what this formula does and how to correct it.

=IFERROR(IF(AND(CS$6>=$M9,CS$6<=$N9),SUMPRODUCT(--(MONTH(ROW(INDIRECT($M9&":"&IF($N9="",TODAY(),$N9))))=MONTH(CS$6))),),"")

Apr 6 in Database by Edureka
• 9,320 points
13 views

1 answer to this question.

0 votes

Anyway, here's an alternative completeness approach based on the conventional formula.

=max(0,min(end1,end2)-max(start1,start2)+1)

for the overlap of two date ranges, which results in

=MAX(0,MIN(IF($B2="",TODAY(),$B2),EOMONTH(DATEVALUE(C$1&"-17"),0))-MAX($A2,DATEVALUE(C$1&"-17"))+1)
enter image description here
answered Apr 11 by Edureka
• 8,820 points

Related Questions In Database

0 votes
1 answer

How to sort dates from Oldest to Newest in Excel?

Drag down the column to select the ...READ MORE

answered Feb 23 in Database by Edureka
• 8,820 points
33 views
0 votes
0 answers

Merging Two excel files as two sheets in one workbook in java

I have two xlsx files at folder ...READ MORE

Mar 10 in Database by Edureka
• 9,320 points
30 views
0 votes
1 answer

How to count distinct values in Excel

Use functions to count the number of ...READ MORE

answered Mar 15 in Database by Edureka
• 8,820 points
34 views
0 votes
1 answer

Count unique values in a column in Excel

Excel may be used to count unique ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
24 views
0 votes
1 answer

Excel formula to remove space between words in a cell

There are three fast techniques to get ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
27 views
0 votes
0 answers

How do I merge two rows in Excel?

Hi I'm not even sure if this ...READ MORE

Apr 4 in Database by Edureka
• 8,820 points
9 views
0 votes
1 answer

How do I split a cell in Excel into two or more where they are divided horizontally?

Table cells should be combined. To make a ...READ MORE

answered Apr 6 in Database by Edureka
• 8,820 points
24 views
0 votes
0 answers

How do I merge two rows in Excel?

Hi I'm not even sure if this ...READ MORE

Apr 7 in Database by Edureka
• 9,320 points
13 views
0 votes
1 answer

Hours and minutes difference between two times in Excel

Using the TEXT function in Excel is ...READ MORE

answered Mar 25 in Database by Edureka
• 8,820 points
21 views
0 votes
1 answer

Merging Two excel files as two sheets in one workbook in java

Basically for this, you need to create ...READ MORE

answered Feb 21 in Database by Edureka
• 8,820 points
21 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP