# Calculating Gaps between dates

##### New Member
Hi,

I have seen an old thread which was helpful. I want to be able to identify gaps between two dates by using the formula that was provided in the old thread: =IF(C7=MAX(C\$2:C\$74),"",IF(SUMPRODUCT((C7+30>=B\$2:B\$74)*(C7< C\$2:C\$74))=0,"Gap: " & TEXT(C7,"DD/MM/YYYY") & " to " & TEXT(MIN(IF(B\$2:B\$74>C7,B\$2:B\$74)),"DD/MM/YYYY"),""))

However, when I tried the above, it is coming back with 1900 as an end date for the gap and also, calculating a whole bunch of other dates and I am not sure where they are coming from?

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Snakehips

##### Well-known Member
00/01/1900 is the base date of Excel and is the result of the date number being zero. Your formula is returning a lot of base dates will be the result of having a lot of empty cells in your B2:B74, C2C74 ranges.
Maybe if you can provide further detail to illustrate typical raw data and your expected result then help will be forthcoming.
If you can download and post a snippet using XL2B in the link below then that would be ideal.

##### New Member
Thank you!

I am unable to upload the mini-sheet via XL2BB

I work for a Healthcare organisation and employ people into Care roles. Part of our job is to ensure that there are no gaps between their recruitment dates from the age of 16, and if gaps are identified, then we ask them to complete a form for the reasons for gaps in employment.

The purpose of this spreadsheet is to identify gaps between dates, and I want column D to calculate the gaps between the dates I am entering into columns B and C.

I hope this makes sense?

#### Fluff

##### MrExcel MVP, Moderator
That formula works fine for me.
+Fluff 1.xlsm
ABCD
1
201/01/202131/01/2021Gap: 31/01/2021 to 22/03/2021
322/03/202119/04/2021
4Gap: 00/01/1900 to 01/01/2021
5Gap: 00/01/1900 to 01/01/2021
6
Lists
Cell Formulas
RangeFormula
D2:D5D2=IF(C2=MAX(C\$2:C\$74),"",IF(SUMPRODUCT((C2+30>=B\$2:B\$74)*(C2< C\$2:C\$74))=0,"Gap: " & TEXT(C2,"DD/MM/YYYY") & " to " & TEXT(MIN(IF(B\$2:B\$74>C2,B\$2:B\$74)),"DD/MM/YYYY"),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Did you confirm it with Ctrl Shift Enter rather than just Enter?

#### Snakehips

##### Well-known Member

@SadiaAslam My apologies. @Fluff is of course correct. I totally missed that it was an array formula requiring to be confirmed with Ctrl + Shift + Enter

##### New Member
OMG.......... I can't believe it was that simple.......

Thank you so much!

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
15
Views
223
Replies
9
Views
81
Replies
3
Views
66
Replies
3
Views
74
Replies
3
Views
69

1,141,204
Messages
5,704,951
Members
421,372
Latest member
Jamie11

### 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