Clarification:

Custom format B2:O2 as h:mm;;;
That will suppress the results that end up as 0.

That will suppress the *display* of results that end up as 0.

The cell will contain numeric 0 you just won't see it.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Here's one way...

On Sheet2 you'll have to include dates as a column header and you'll have
to repeat the date like this:

|8/16/2009......|8/16/2009....|8/17/2009.......|8/17/2009......|
| Sunday Start | Sunday End | Monday Start | Monday End |

Let's assume your data on Sheet1 is in the range A2:D9

Sheet2 B1:O1 = dates as shown above
Sheet2 A2 = some ID number

Enter this formula in B2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$9=$A2),--(Sheet1!$B$2:$B$9=B$1),INDEX(Sheet1!$C$2:$D$9,,COU NTIF($B$1:B$1,B$1)))

Copy across to O2

Custom format B2:O2 as h:mm;;;

That will suppress the results that end up as 0.

--
Biff
Microsoft Excel MVP


"Sara" wrote in message
...
Hi there

I'm trying to create a index/match formula for rosters.

Sheet 1 looks like this
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

And I'm trying to change it into the following format on sheet 2

ID | Sunday Start | Sunday End | Monday Start | Monday End | Tuesday
Start
.etc


No one works all 7 days of the week so I know I need some sort of ISERROR
or
ISNA as well

The frustrating thing is that I did this last year and can't find the
answer