hello,

Below is the annual timesheet requirement, and I need it preferrably in
Excel formula (if not VBA, will also do)

Sheet 1:
Date Day Hours Remarks
1/1/06 Sun 0
1/2/06 Mon 0 New Years Day Holiday
1/3/06 Tue 8
1/4/06 Wed 8
1/5/06 Thu 8
1/6/06 Fri 8
1/7/06 Sat 0
1/8/06 Sun 0
1/9/06 Mon 8
....
....and so on

Sheet 2:
Begin End Total hours
1/2/06 1/15/06 72
1/16/06 1/29/06 80
....
....and so on

What I am looking here is, In sheet 2, Total hours should be calculated
based on looking up Begin and End dates from sheet 2 in sheet 1 and
then computing sum for that range. For example, the formula should
lookup the range of dates 1/2/06 and 1/15/06 in sheet 1 and compute the
sum as 72.

Appreciate your quick response.

-King

=sumif(Sheet1!A:A,">="&A2,Sheet1!C:C) - Sumif(Sheet1!A:A,">"&B2,Sheet1!C:C)
in C2
then drag fill down column C of Sheet2

--
Regards,
Tom Ogilvy


"king" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> hello,
>
> Below is the annual timesheet requirement, and I need it preferrably in
> Excel formula (if not VBA, will also do)
>
> Sheet 1:
> Date Day Hours Remarks
> 1/1/06 Sun 0
> 1/2/06 Mon 0 New Years Day Holiday
> 1/3/06 Tue 8
> 1/4/06 Wed 8
> 1/5/06 Thu 8
> 1/6/06 Fri 8
> 1/7/06 Sat 0
> 1/8/06 Sun 0
> 1/9/06 Mon 8
> ...
> ...and so on
>
> Sheet 2:
> Begin End Total hours
> 1/2/06 1/15/06 72
> 1/16/06 1/29/06 80
> ...
> ...and so on
>
> What I am looking here is, In sheet 2, Total hours should be calculated
> based on looking up Begin and End dates from sheet 2 in sheet 1 and
> then computing sum for that range. For example, the formula should
> lookup the range of dates 1/2/06 and 1/15/06 in sheet 1 and compute the
> sum as 72.
>
> Appreciate your quick response.
>
> -King
>

Tom,

Thanks a lot !! It worked just fine....

Regards,
King

Tom Ogilvy wrote:
> =sumif(Sheet1!A:A,">="&A2,Sheet1!C:C) - Sumif(Sheet1!A:A,">"&B2,Sheet1!C:C)
> in C2
> then drag fill down column C of Sheet2
>
> --
> Regards,
> Tom Ogilvy