Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Annual Timesheet: Two date ranges lookup in a single sum column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Annual Timesheet: Two date ranges lookup in a single sum column
=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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Annual Timesheet: Two date ranges lookup in a single sum column
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting ranges of various column lengths, lookup values in columns | Excel Worksheet Functions | |||
Financial rear date ranges in a single cell | Excel Discussion (Misc queries) | |||
Matching two column ranges with a date | Excel Worksheet Functions | |||
date in column.. need to put in a single row | Excel Discussion (Misc queries) | |||
TIMESHEET LOOKUP | Excel Discussion (Misc queries) |