need multifunctional formula or macro

[vj2lip]

hi im stuck with my project. prj.jpg
i need formula which could identify periods for value i enter like "aa" starting from January. 22-23 Jan aa 1 pair then 05-14 feb aa1 pair and 16 feb aa 1 pair so total is 3 pairs of absence and it should recognize periods by shift pattern (so if 4 off on shift [blue] + 2 off on shift [blue] than "aa" 1 pair of absence, if 4 off on shift [blue] + 2 off on shift [blue] and next shift 1 off than total 2 pairs.

i would really appreciate if you could help me with this.

Kind regards VJ

[bebo021999]

Could you post a workbook?

[vj2lip]

hi

here is a link to *.xlsx https://app.box.com/s/dfovr083em6yacf42sn2q3mr7jqqaic4

in sheet "Acalendar" i done so far only countif but i try to make "Acalendar" transfer periods in sheet "Adates" i done in red date which means one period [period keep counting as 1 if day off "AA" goes in a row by shift patern] if for instance there is one gap in shift pattern without "AA" and starts again that means 2 periods.

Thanks one more time
VJ

[FDibbins]

vj2, welcome to the forum

2 things...
Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

and please post your WB to the forum, not all members are able - or willing - to access file-hosting sites

[vj2lip]

Hi again here is attached file of my project use previous post to understand needs... So far i am trying to quick learn how to use excel vba because i think it could be better by clicking button and get report with criteria i need to be shown.

unfortunately i still cannot figure it out how to generate periods by shift pattern

Regards
VJ

[FDibbins]

unless there is some logic behind how you apply the colors for shifts, then you will need VBA for this - regular formulas cannot "see" colors.

[vj2lip]

hi FDibbins

no mate nothing special for colouring cells simple colour filling. I realized that there is no way i can do what i want with formulas so i started to learn VBA because i have no clue how to use VBA and it looks like for that what i need it will be not so simple code.

Thanks
VJ

[FDibbins]

OK, well perhaps once you have complied with the 1st request in post #4, members will offer suggestions?

[vj2lip]

Hi again guys,

i still working on this project but now i try to get report and pivot table seems to give wrong report (I just need after choosing name to get info about name ) it sums every thing and I cannot add 2 worksheets to pivot table report.

Regards

VJ @ Tulip Ltd

[FDibbins]

OK, let me make this clear for you, seeing as 2 polite requests have gone unnoticed...

Change your thread title please, no other members will give suggestions until you have complied with that.