Groups > microsoft.public.excel.programming > #108024

trying to sum range in one sheet depending on the date in cell on other sheet

Path	csiph.com!xmission!news.glorb.com!peer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!Xl.tags.giganews.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!local2.nntp.dca.giganews.com!nntp.newsgroupbanter.com!news.newsgroupbanter.com.POSTED!not-for-mail
NNTP-Posting-Date	Wed, 23 Sep 2015 03:49:02 -0500
From	tomtic56 <tomtic56.10481cf8@excelbanter.com>
Newsgroups	microsoft.public.excel.programming
Subject	trying to sum range in one sheet depending on the date in cell on other sheet
Date	Wed, 23 Sep 2015 09:47:39 +0100
Message-ID	<tomtic56.10481cf8@excelbanter.com> (permalink)
Organization	ExcelBanter.com
User-Agent
X-Newsreader
X-Originating-IP
Lines	40
X-Usenet-Provider	http://www.giganews.com
X-Trace	sv3-kj1OZgX55DQWu2nA9I0VGZMd3KrHTdmHmpR8ncCRtbLOCXVgyyVbMQFfXoWhFjDsJPZp+0WEb4NbwOJ!oZzkQcVwDtQJVG72xGmXjVQ4w5JfhexzDZntakCPifE9zV70cyJjCIOpImmagycdGFtFk9PFl9EH!E/UjFk/WyA==
X-Complaints-To	abuse@newsgroupbanter.com
X-Abuse-and-DMCA-Info	Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info	Otherwise we will be unable to process your complaint properly
X-Postfilter	1.3.40
X-Original-Lines	24
X-Original-Bytes	2307
X-Received-Bytes	2441
X-Received-Body-CRC	3683137996
Xref	csiph.com microsoft.public.excel.programming:108024

Show key headers only | View raw

Hi all, I have a spreadsheet and am trying to calculate hours worked
using 2 sheets.
sheet1 is entitlement calculation and sheet2 is public holidays

I am trying to sum the hours worked on sheet2 "public holiday" range c2 
; to c9 depending on the date on cell B12 of sheet 1 "entitlement
calculation"
starting in with the date  in sheet 1  "entitlement calculation" cell
b12 as shown in the sample below

SAMPLE: Sheet 1.."entitlement calculation" cell B12 is the start date
which could be the 1st of any month..in the sample case it is
01-Oct-2015 which then sums the range hours worked (c2:c9) starting at
date > 30-sep-2015 (c6) which gives 7 hours which is correct,
however the next entry into cell B12 might be 01-may-2015 which would
sum range hours worked from  > 30-apr-2015 which would be (c4:c9) which
would give 10.5 hours.


sheet 2 is public holidays as shown below.

COL A     COL B         COL C
Day	     Date	      Hours worked
Friday     03/04/2015	  3.5
Monday   06/04/2015	  0
Monday   04/05/2015	  3.5
Monday   28/09/2015	  0
Friday   25/12/2015       3.5
Monday  28/12/2015	  0
Friday   01/01/2016       3.5
Monday 04/01/2016	  0

I have tried using various if statements but cant get the correct answer
for all options..any help would be much appreciated..thanks




-- 
tomtic56

Back to microsoft.public.excel.programming | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

trying to sum range in one sheet depending on the date in cell on other sheet tomtic56 <tomtic56.10481cf8@excelbanter.com> - 2015-09-23 09:47 +0100
  Re: trying to sum range in one sheet depending on the date in cell on other sheet Claus Busch <claus_busch@t-online.de> - 2015-09-23 12:20 +0200

csiph-web