# how to extract schedules and rest days

#### Rhon Alvarez

##### Member
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.

#### Attachments

• 158.3 KB Views: 8

#### 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 Pacris 8:00:00 8:00:00 8:00:00 OFF - OFF 9:00:00 8:00:00 8: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"...

#### Rhon Alvarez

##### 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 Pacris 8:00:00 8:00:00 8:00:00 OFF - OFF 9:00:00 8:00:00 8: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

• 184.8 KB Views: 4