hi,

it is an array formula to insert with CTRL+Shift+Enter

=INDEX($A$1:$AX$1,IFERROR(SMALL(IF($C2:$AX2="x",CO LUMN($C2:$AX2)),1),""))&":"&IF(ISEVEN(IFERROR(SMAL L(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),"")),"30"," 00")&"
-
"&INDEX($A$1:$AX$1,IFERROR(LARGE(IF($C2:$AX2="x",C OLUMN($C2:$AX2)),1)+1,""))&":"&IF(ISEVEN(IFERROR(L ARGE(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),"")),"00 ","30")

isabelle

Le 2015-10-28 10:41, a écrit :
Hello,

Have a bit of an odd but challenging request for the group here!

The objective for this project is to take a details work schedule which shows
tasks by half hour increment and automatically calculate a summary schedule
for the entire week showing the employee's start and end time.

I have a simple template for one day of the week which I've uploaded for you
to understand the results I'm looking for:

https://www.dropbox.com/s/gsfp5tt3uw...late.xlsx?dl=0

In rows 1 - 3, this is the detailed schedule that would be input by the user.
They simply enter letters based on the tasks required at the point during
their shift. Row 1 shows the time in half hour increments so when Employee 1
has an X in column W, their shift will start at 10:00am. For the same
employee, the last X they have for that day is in column AN which means that
their shift will end at 19:00 (or 7pm).

For Employee 2, the same rules apply but this employee's tasks start in
column T which would signify 8:30am and their last X comes in column AK which
ends in 17:30.

So this top section is fine and somewhat simple.

The part I need help with is the bottom section which is the summary, rows
6-10.

I want to automate this section based on the detailed schedule being entered
in the section above. Ideally, via formulas this summary section would
automatically update based on the schedule being entered. The end result
would be summary in one cell for the day that shows their start and end time.
I've entered examples of this in the spreadsheet and highlighted this in
orange.

I hope that all makes sense.

I should also mention that I cannot use macros here. I've tried to do some
sort of lookup using HLOOKUP and INDEX but unable to figure out how to
automate the process.

Thanks in advance for your help!