Calculating Gaps between dates

SadiaAslam

New Member
Joined
May 17, 2021
Messages
3
Office Version
  1. 2013
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?

Please help! :)

1621263549595.png
 

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
Joined
May 17, 2009
Messages
5,357
Office Version
  1. 2013
Platform
  1. Windows
@SadiaAslam Welcome to MrExcel.
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.
 

SadiaAslam

New Member
Joined
May 17, 2021
Messages
3
Office Version
  1. 2013
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
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 17, 2009
Messages
5,357
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

@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
 

SadiaAslam

New Member
Joined
May 17, 2021
Messages
3
Office Version
  1. 2013
OMG.......... I can't believe it was that simple.......

Thank you so much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top