BLANK AND ZERO CELLS IN DATE CALCULATIONS

[akasmia]

I have a table with the following dates:
Excel sheet:
DATE1: DATE2: DATE3: DATE4:

Iam tryin to use MIN(DATE1, DATE2, DATE3, DATE4) and put the result in a cell. Some dates can be zeros or blanks (missing). MIN function works correctly only if all dates are given(available). I want to be able to use only non-blank and non-zero cells in this funtion in order to get accurate results based only the cells that contain information (i.e. not the zeros or blanks). Is there a way of doing this?. I prefer Excel formuals, but if not then VBA code will do!.

Appreciate it!

 

[xlhelp]

=SMALL(A22,COUNTIF(A22,"0&quot+1)

You will need to format the cell for date.

HTH

Indu bid u adieu for a while after Aug 15. Need to look for employment. For any of my unfinished threads, please write to ishukla@yahoo.com

 

[akasmia]

Thanks xlhelp for your great help!.

I have tried the fomula: SMALL(A22,COUNTIF(A22,"0&quot+1)

with two zero cells and again I received #NUM!

Example:
DATE1: Date2: Date3: Date4:
03/31/02 05/01/01
will result in #NUM!. If I remove the +1 then it works for fewer dates.

I will try to play with it a bit more, perhaps inlude a count blanks conditional statement and if >1 blank/zero then use the formula without the +1. Hope so!

Let me know if you can help again

 

[xlhelp]

Hi, I have tried it every way, including text. etc and seems to work for me. I have just started using XP so, I don't know if XP ignores some of these things. I will not have access to XL2K or XL97 for a few days; I will try it again at that time, but otherwise the formula works for me.

Indu bid u adieu for a while after Aug 15. Need to look for employment. For any of my unfinished threads, please write to ishukla@yahoo.com

 

[wwgmr]

Akasmia, I was looking at your problem and I wanted to check. Your tring to show the lowest date in range correct? This done with =min(a2:d2) it will ignore any blank cells by default. I have tested with it on both XP version and 2k and it worked fine. Try it again maybe there was mistake in formula?

Hope this helps

Eric