• 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

  • schedules.xlsx
    158.3 KB · Views: 8
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
 
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

  • schedules(BY).xlsx
    184.8 KB · Views: 4
Back
Top