Consecutive dates excel

[sjurawan]

Hi,

Having some problems trying to work out a formula to count incidents of absence. I have a report which gives me a list a column of names and next to it dates they have been absent. I want to work out the incidences of absence, e.g Johan - 01/01/2017, 02/01/2017, 03/01/2017, 10/02/2017 in this example Johan has been absent for 4 days but it was only 2 incidences of absence.

If you need an example workbook I can upload one, thank you for any help you can give me

Kind Regards
Sam

 

[lbobbio1988]

Sam,

My suggestion would be to use a "helper" sheet. This sheet would perform checks on each set of dates and record either a 0 or a 1, depending on whether that date should be counted or not. The original data sheet would then sum the values in the helper sheet to get the number of non-consecutive absences. The helper sheet could then be hidden if you wished. I cannot yet post attachments, but would be happy to e-mail an example to you if you'd like.

 

[Scott Huish]

My suggestion would be to use SUMPRODUCT. In my example, I am using American style dates.

Excel 2010
	A	B	C	D
1	1/1/2017			2
2	1/2/2017
3	1/3/2017
4	2/10/2017
Sheet1

Cell Formulas
Range	Formula
D1	=SUMPRODUCT(--(A1:A10<>A2:A11-1),--(A1:A10<>""))