IF / AND function and Date range

Closed
Magoo2015 Posts 2 Registration date Thursday January 15, 2015 Status Member Last seen January 15, 2015 - Jan 15, 2015 at 04:09 PM
Magoo2015 Posts 2 Registration date Thursday January 15, 2015 Status Member Last seen January 15, 2015 - Jan 15, 2015 at 05:09 PM
I am trying to create a spreadsheet that tracks when jobs commence and complete. I have been working with the IF and AND functions but can't quite seem to get the response I want.

Essentially, if the "Completion Date" for any one of a number of jobs is after x date but before y date, I want it to return a response of -1, otherwise I want it to return a response of 0. I have a number of jobs to track and I created the following formula

=IF($J19<I$33,-1,0)+IF($J20<I$33,-1,0)+IF($J21<I$33,-1,0)+IF($J22<I$33,-1,0)+IF($J23<I$33,-1,0)+IF($J24<I$33,-1,0)+IF($J25<I$33,-1,0)+IF($J26<I$33,-1,0)+IF($J27<I$33,-1,0)

Which works to a certain extent but cumulates the responses, hence trying to create a range. If a job is due to complete in any give month, I want it to come off the tally (i.e. -1). The issue I have with the above formula is that it cumulates the responses so that, for example, a job ending in July is reported as ending in August, September etc.

Any help would be greatly appreciated.
Related:

3 responses

Blade_Hunter Posts 2 Registration date Tuesday January 13, 2015 Status Member Last seen January 15, 2015
Jan 15, 2015 at 04:40 PM
Use an array formula:

=SUM((J20:J27<I33)*-1)

Instead of pressing enter after inputting, press CTRL-SHIFT-ENTER

Cheers

Dan
0
Blade_Hunter Posts 2 Registration date Tuesday January 13, 2015 Status Member Last seen January 15, 2015
Jan 15, 2015 at 04:41 PM
Let me know the range with the dates and what you want the test to do and we can add to the array formula
0
Magoo2015 Posts 2 Registration date Thursday January 15, 2015 Status Member Last seen January 15, 2015
Jan 15, 2015 at 05:09 PM
Cheers Dan

This is a better solution than I had (and a good way to start to learn about array formula) but unfortunately still cumulates the response. If a job has been completed in July I don't want it to report as completed again in August, September etc...

I have a column with job completions and I am charting, month by month for the calendar year, how many jobs we have on the go so I have "existing", "proposed" and "change" rows (the change is where I am putting the array formula).

I'm not sure if that sheds any additional light?
0