Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

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

Show all headers | 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

#108024 
  #108025 

csiph-web