Putting a formula into a cell if it falls between a date range

**BakerD** · 02-18-2009, 07:45 AM

Hi All,

This is my first thread, so please be gentle with me!

I am trying to create a spreadsheet that will populate a % of a resources time required between certain dates

For example, this is the data being provided:
Resource % start end
A 10 01/01/2009 01/03/2009
A 5 01/04/2009 01/07/2009
B 20 01/02/2009 01/06/2009

As an output, i need to get the data to be shown by resource and by month as a %

For example
Resource Jan Feb Mar Apr May Jun
A 0.1 0.1 0 0.1 0.1 0.1
B 0.1 0.1 0.1 0.1 0.1 0

Does anybody know if excel can perform this?
Currently I am working with formula's like:

=IF(a:a=a2,(=if(C2<="Feb"&D2>="feb",(B2/100),0)),0)

But it is returning #VALUE...

At a loss, and any help would be greatly appreciated, even if it is to say that Excel cant do this.

Cheers,
Dave

**DonkeyOte** · 02-18-2009, 07:55 AM

Dave,

Welcome to the Board.

Please see attached - is this along the right lines ?

Note: pending volume of data you may want to think of an alternative approach.

**BakerD** · 02-18-2009, 08:27 AM

DonkeyOte....Nailed on! that is exactly what i was after!!! Thank you kindly!

One quick question though, i have a number of different resources, and would like to copy the formula into all respective cells, but the arrays keep changing when i drag the cursor along to fill series/copy cells....is there any way of stopping this happening and just getting certain searches to change?

Cheers,
Dave

**DonkeyOte** · 02-18-2009, 08:42 AM

Not quite sure I follow ... in the example the formula can be copied down and across and the only things that should alter are:

a) the reference to the ID (will alter as you go down)
b) the reference to the date column (will alter as you go across)

Without seeing how you're implementing I'm afraid it's hard to comment further... if you're not familiar with absolute & relative referencing check out XL Help for an example (see also: http://www.techonthenet.com/excel/qu...eferencing.php)