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