Microsoft Excel - Please Help !! Excel Range !! Identifying Missed Timeline

Asked By Rajender Prasad on 21-Oct-13 03:41 PM

Dear All,

I have two set of ranges in excel like below. Now, I would need to identify which months or Timline is missing from the first one. first range I have it very straight however second one split into to different timelines. PLease help me to identify which time line is missing in second as compare to first one in excel.

1/1/2026		1/1/2026
1/1/2026	12/31/2013	1/1/2026	12/31/2013
1/1/2026	12/31/2012	1/1/2026	12/31/2012
1/1/2026	12/31/2011	1/1/2026	12/31/2011
1/1/2026	12/31/2010	1/1/2026	12/31/2010
1/1/2026	12/31/2009	7/1/2026	12/31/2009
1/1/2026	12/31/2008	1/1/2026	6/30/2009
		8/1/2026	12/31/2008
		1/1/2026	6/30/2008

Harry Boughen replied to Rajender Prasad on 24-Oct-13 02:10 AM

Hi Prasad,
Try looking at the difference between the end of one period and the start of the next and if it is more than one then there is part of the timeline missing.
Regards
Harry

Rajender Prasad replied to Harry Boughen on 24-Oct-13 12:18 PM

Harry, am so sorry, I am not able to understand how to start also on this..but it resolves many of my problems in automation. Could you please write the code for me ?? please please..

Regards,
Prasad

Harry Boughen replied to Rajender Prasad on 26-Oct-13 09:02 PM

Hello Prasad,
In the column next to your series with missing ranges use the following formula (assuming that the data is in columns E and F and start at row 1.

=IF(E1-F2>1,TEXT(F2+1,"dd/mm/yyyy")&" to "&TEXT(E1-1,"dd/mm/yyyy")&" is missing","")

Regards
Harry