Consecutive dates to FROM and TO

[janarthenan]

Hello All

Happy New Year.

I hope someone will be able to advise a process to achieve the below:
I have a system where I can pull sickness data from a company - the data is in an excel format and shows the name and the date they were sick.
If they were off consecutively each date is shown in a new row.

I need the date to show as "from" and "to" - if it is only one date they were off the from and to date should be the same, if their sickness was consecutive it should show the first in the sequence as from and the last in the sequence as to.

Thanks
J

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[JohnTopley]

With helper column

set C3=1

in C4 and copy down

=IF(A4=A3,IF(B4=B3+1,C3,C3+1),C3+1)

in F3

=INDEX($A$3:$A$1000,MATCH(ROWS($1:1),$C$3:$C$1000,0))

in G3

=INDEX($B$3:$B$29,MATCH(ROWS($1:1),$C$3:$C$29,0))

in H3

=INDEX($B$3:$B$29,MATCH(ROWS($1:1),$C$3:$C$29,0))+COUNTIFS($A$3:$A$29,K3,$C$3:$C$29,ROWS($1:1))-1

[BMV]

Please Login or Register  to view this content.

[janarthenan]

Hi BMV and John
Thank you very much for the solutions - they both do exactly what I required.

I do have a slight variation to it and have tried to modify the formulas to achieve the same result but not getting anywhere.
I have an additional column - called shift, if there is an offday in between two sick dates - this should be considered as one episode.

J

[JohnTopley]

From your data, the "Off Day" makes no diiference to the results as the column is not considered in the formula.

[janarthenan]

Hi John

Thanks for the quick reply.
Apologies, I forgot to include the crucial information as there can be working days in between.
J

[JohnTopley]

New helper in column E

=IF($B4="WorkingDay",0,IF($B3="WorkingDay",MAX($E$3:E3)+1,IF(A4=A3,IF(C4=C3+1,E3,E3+1),E3+1)))

[janarthenan]

Works exactly as I require.
Thank you for all the help! I am very grateful!

J

[janarthenan]

Hi John

Apologies - found an issue.
If an OffDay is between two WorkingDays, It's counting them as Sick Days.
See attached - highlighted in orange.

I spent an whole afternoon trying to understand the code, which I did but I couldn't get it to ignore the offDays.

J

[JeteMc]

Try pasting the following into cell E4 and then double clicking the fill handle to copy down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[janarthenan]

Thank You!

[JeteMc]

You're Welcome. Thank You for marking the thread as 'Solved'. I hope that you have a blessed day.