• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to extract schedules and rest days

Hi Team,
Kindly help me create formula to extract a certain person's daily schedules and scheduled rest days for the week based from raw data from different sheet.

please find attached file

thank you in advance.

62309
 

Attachments

GraH - Guido

Well-Known Member
Hi,

I detected some volatile data and thus my formulae return some errors.

What if a person has 2 schedules, like in row 16

Mr. Florencio Jr Balolong Pacris8:00:008:00:008:00:00OFF - OFF9:00:008:00:008:00:00

Also you have schedules like 16:00 - 01:00 or like in the example "8:00:00".
There is "OFF-OFF" and "OFF"...
 
Hi,

I detected some volatile data and thus my formulae return some errors.

What if a person has 2 schedules, like in row 16

Mr. Florencio Jr Balolong Pacris8:00:008:00:008:00:00OFF - OFF9:00:008:00:008:00:00

Also you have schedules like 16:00 - 01:00 or like in the example "8:00:00".
There is "OFF-OFF" and "OFF"...
Hello,

we may disregard other schedules as shown for we are based on the start of the week and not on any other day.

thanks,
Rhon
 

bosco_yip

Excel Ninja
Try...........

In C2, copied down :

=IF(D2="","",IFERROR(INDEX('Kboi''s Sched File'!F5:L5,MATCH(1,INDEX(0+ISNUMBER(-LEFT('Kboi''s Sched File'!F5:L5)),0),0)),'Kboi''s Sched File'!F5))

In D2, copied right to E2 and all copied down :

=IFERROR(INDEX('Kboi''s Sched File'!$F$3:$L$3,AGGREGATE(15,6,COLUMN('Kboi''s Sched File'!$A$1:$G$1)/(LEFT('Kboi''s Sched File'!$F5:$L5,3)="OFF"),COLUMN(A1))),"")

Regards
Bosco
 

Attachments

Top