Format Cell

[Pran]

Hi,

I m using vlookup function which is result is date for each cell and i
already formatted my workbook with date category.

How ever, not every cell in my source has a date, many of datas are blank
and result shows "0-Jan-00" rather than "-" or just "blank".

How can I change this?

Any help is much appreciated.

 

[Jacob Skaria]

modify your vlookup formula to

=IF(vlookupformula>0,vlookupformula,"")

If this post helps click Yes

 

[Pran]

Jacob,

Remember this formula below that you gave me earlier?
It is now related with the formula that you just gave me later, and the
result now is not appropriate for that count since all empty cell also
counted.
Could you help me to revise the counta array?

here's the case :

1. X MAST 2009 (=IF(vlookupformula>0,vlookupformula,"") till down
2. date
3. date
4. date
5. Blank date
6. Blank date
7. Blank date
8. Blank date
9. Blank date
10. Blank date
11. EID 2010
12. EID 2010

Total 12 (should be 6 but empty cells are counted)
Remain 0 (should be 6)




_____________________________________________
Try
=COUNTA(H13:H16)-COUNTIF(H13:H16,">=" & TODAY())

If this post helps click Yes
---------------
Jacob Skaria

I have four cells that contain
number,
text,
date,
#n/a

How can i count all four cells using counta but date only will be counted if
less than criteria that i've made <example : less than today()>

Thx a lot,

___________________________________________________

 

[Jacob Skaria]

I do remember..Can we try Countif >0 as below

=COUNTIF(H13:H16,">0")-COUNTIF(H13:H16,">=" & TODAY())

But what is 'EID 2010' is that a text

If this post helps click Yes

 

[Pran]

Yes, 'EID 2010' is a text and should be counted too.
I think counta array should exist ?

 

[Jacob Skaria]

=SUMPRODUCT(--(LEN(TRIM(H13:H16))>0))-COUNTIF(H13:H16,">=" & TODAY())

If this post helps click Yes

 

[Ashish Mathur]

Hi,

This will count all the cells which have a length of 0.


=SUMPRODUCT(1*(LEN(D1519)>0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Rick Rothstein]

I'm thinking you meant to say "...which have a length GREATER than 0".

 

[Ashish Mathur]

Sure - that is right. Thank you

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com