Can Excel return the earliest date using =IF?

**jareduk** · 04-01-2009, 04:08 AM

Hi all

I have been doing a bit of searching on the web but cant seem to find the exact answer I am looking for, or if I have found something my knowledge is not quite up to scratch as how to change said formula to suit my needs.

At work my colleague and I have basically created an excel appointments diary to book patients into.

We have separate sheets per physician and on these sheets we have created a 'box' for each date. With each of the box's we have a count function to return the number of booked, available and cancelled spaces.

What we want to do is have a formula that returns to us the earliest possible date with space available. Effectively we have this:

Date # of patients
1.4.09 0
3.4.09 0
20.4.09 8
21.4.09 1
21.4.09 0
23.4.09 12
24.4.09 4
27.4.09 9
28.4.09 8
30.4.09 14

What formula would you suggest using? I did try using a nested IF function i.e. =IF(B1>=1,A1,value_if_false, IF(B2>=1,A2,value_if_false)). The value_if_false is what I had a problem with as if it is false I wanted the formula to look for the next date available with spaces. I always got a 'too many arguments' error returned.

Any help would be much appreciated, or just pointing me into the right direction would be great.

Cheers

Jared

**arthurbr** · 04-01-2009, 04:17 AM

Hi and welcome to the board,

what do you exactly mean by " space available"? number of patients = 0?

**JBeaucaire** · 04-01-2009, 04:26 AM

No, i misread the problem, thrown off by the title of the thread, My apologies.

**jareduk** · 04-01-2009, 04:28 AM

Thanks

Sorry, looking at my post I appear to have named it wrong I meant to put number of spaces!

Date # of spaces
1.4.09 0
3.4.09 0
20.4.09 8
21.4.09 1
21.4.09 0
23.4.09 12
24.4.09 4
27.4.09 9
28.4.09 8
30.4.09 14

So 0 meaning no appointment slots for patients and anything >0 having slots available.

Cheers

Jared

**JBeaucaire** · 04-01-2009, 04:34 AM

If you can provide a simple key noting which days are full, then INDEX/MATCH on the word "Free" to bring back the date that matches the first instance of "free space."

**DonkeyOte** · 04-01-2009, 04:36 AM

Or, you can use a MIN(IF( array assuming dates are date values

=MIN(IF(B1:B100>0,A1:A100))
committed with CTRL + SHIFT + ENTER

Where B1:B100 contains spaces and A1:A100 dates.

**jareduk** · 04-01-2009, 04:54 AM

Thanks all for the rapid replys! I have now sorted my problem. Now I just need to improve my knowledge of excel... trial and error!

JBeaucaire: Thanks for that, works a treat!

Jared