# I think an easy one to do with dates

#### vbnoob

##### Board Regular
Hi,

I think this is an easy one but I cant get my head around it this morning. I think its a monday thing, that or Im just not clever.

I have got a start date in Cloumn C and an end date in Column D. I want to only get how many days are in Feb within that record.

I have put a couple of examples below

C D E
Start Date End date Days
27/1/2009 12/2/2009 12 Days
12/1/2009 24/3/2009 28 days
27/2/2009 4/3/2009 2 days

I cant think of the formula that needs to go into Column E. I have over 400 records and dont fancy a day looking at dates and know there must be a quick way.

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Perhaps:

=MIN(D2,"28/02/2009"+0)-MAX(C2,"01/02/2009"+0)

#### Richard Schollar

##### MrExcel MVP
You could do:

=MAX(0,MIN(C2,DATE(2009,3,0))-MAX(D2,DATE(2009,2,0)))

where you will need to alter the 2009 value to the February year of interest. There's probably a much slicker way of doing this...

#### vbnoob

##### Board Regular
Perhaps:

=MIN(D2,"28/02/2009"+0)-MAX(C2,"01/02/2009"+0)

Start End Days
<TABLE style="WIDTH: 159pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=213 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" span=2 width=71><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=71 height=17>21/02/2009</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=71>22/02/2009</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=71 x:fmla='=MIN(B1,"28/02/2009"+0)-MAX(A1,"01/02/2009"+0)' x:num>27</TD></TR></TBODY></TABLE>

It gave this result for a couple of similar records. Was also counting 25/1/2009 12/2/2009 as 11 days. Is there a way of tweaking it?

#### vbnoob

##### Board Regular
You could do:

=MAX(0,MIN(C2,DATE(2009,3,0))-MAX(D2,DATE(2009,2,0)))

where you will need to alter the 2009 value to the February year of interest. There's probably a much slicker way of doing this...

Richard, Was giving me this result, didnt seem to work on quite a few of them.

<TABLE style="WIDTH: 159pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=213 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" span=2 width=71><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=71 height=17 x:num="39871">27/02/2009</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=71 x:num="39872">28/02/2009</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=71 x:num x:fmla="=MAX(0,MIN(A1,DATE(2009,3,0))-MAX(B1,DATE(2009,2,0)))">0</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 159pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=213 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" span=2 width=71><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=71 height=17 x:num="39868">24/02/2009</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=71 x:num="39869">25/02/2009</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=71 x:num x:fmla="=MAX(0,MIN(A1,DATE(2009,3,0))-MAX(B1,DATE(2009,2,0)))">0</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 159pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=213 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" span=2 width=71><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=71 height=17 x:num="39860">16/02/2009</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=71 x:num="39933">30/04/2009</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=71 x:num x:fmla="=MAX(0,MIN(A1,DATE(2009,3,0))-MAX(B1,DATE(2009,2,0)))">0</TD></TR></TBODY></TABLE>

#### Richard Schollar

##### MrExcel MVP
I got the start and end round the worng way - should be:

=MAX(0,MIN(D2,DATE(2009,3,0))-MAX(C2,DATE(2009,2,0)))

#### pgc01

##### MrExcel MVP
Hi

Another option. In E2:

=IF(OR(D2<"2009-02-01"+0,C2>"2009-02-28"+0),0,MIN(D2,"2009-02-28"+0)-MAX(C2,"2009-02-01"+0)+1)

Copy down

#### vbnoob

##### Board Regular
Cheers for all the help guys, was much appreciated.

Replies
0
Views
481
Replies
0
Views
182
Replies
5
Views
530
Replies
4
Views
460
Replies
2
Views
520

1,191,718
Messages
5,988,264
Members
440,146
Latest member
rgomes8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back