with a start or finish time of midnight

Well, I didn't think about midnight!

If you format your times on Sheet1 as [h]:mm and enter 12:00 AM as 24:00...

Then on Sheet2 change the custom format to [h]:mm;;;

Here's a small sample file with those changes implemented.

xSara1.xls 20kb

http://cjoint.com/?iniFhYuAZR


--
Biff
Microsoft Excel MVP


"Sara" wrote in message
...
Cheers for your help Biff
I had a few problems and when copying across the formula had #VALUE as a
result in most cells. I couldn't follow the formula to identify the
issue.
In all the cells that had a result of "0", the formatting changed to 0:00

So I ensured that anyone with a start or finish time of midnight became
"00:00:01" on sheet 1, and changed the Custom Format to "HH:mm" for 24
hour
format.


Cheers Domenic - but I got completely lost.

I managed to do the following:

Created a separate sheet (sheet 2) with the unique records from the
advanced
filter (omitting all shift data)

Defined the columns in Sheet 1
Column A = ID (A1:A381)
Column B = SHIFT (B1:B381)
Column C = START (C1:C381)
Column D = END (D1:D381)

ID | Shift Day | Start | End
113809 | 17-Aug | 13:00 | 17:00
113809 | 18-Aug | 13:00 | 17:00
113809 | 19-Aug | 13:00 | 17:00
113809 | 20-Aug | 13:00 | 17:00
113809 | 21-Aug | 13:00 | 17:00
148044 | 17-Aug | 13:00 | 20:35
148044 | 18-Aug | 13:00 | 20:35
148044 | 19-Aug | 13:00 | 20:35

Sheet 2

Altered to:
| A | B | C | D | E
| F | G
ROW 1 | | 16-Aug | 16-Aug | 17-Aug | 17-Aug | 18-Aug
| 18-Aug
ROW 2 | ID | Sun Start | Sun End | Mon Start | Mon End | Tues Start |
Tues
End

Then the array formula becomes: (found the file from last year)

Start Times
=IF(ISNA(INDEX(START,MATCH(1,(ID=$A3)*(SHIFT=B$1), 0))),"",INDEX(START,MATCH(1,(ID=$A3)*(SHIFT=B$1),0 )))

End Times
=IF(ISNA(INDEX(END,MATCH(1,(ID=$A3)*(SHIFT=C$1),0) )),"",INDEX(END,MATCH(1,(ID=$A3)*(SHIFT=C$1),0)))

I don't understand how the formulas work
I do get:
=IF(ISNA(formula,"")
Stating that if it's blank - leave blank


=INDEX(START,MATCH(1,(ID=$A3)*(SHIFT=B$1),0))
Retrieve Start time info if the rest matches
But I don't get the MATCH formula with multiplication