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.

modify your vlookup formula to

=IF(vlookupformula0,vlookupformula,"")

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


"Pran" wrote:

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,

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(vlookupformula0,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


"Pran" wrote:

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" wrote:

modify your vlookup formula to

=IF(vlookupformula0,vlookupformula,"")

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


"Pran" wrote:

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.

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
---------------
Jacob Skaria


"Pran" wrote:

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(vlookupformula0,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


"Pran" wrote:

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" wrote:

modify your vlookup formula to

=IF(vlookupformula0,vlookupformula,"")

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


"Pran" wrote:

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.

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Pran" wrote:

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(vlookupformula0,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


"Pran" wrote:

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" wrote:

modify your vlookup formula to

=IF(vlookupformula0,vlookupformula,"")

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


"Pran" wrote:

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.

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

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


"Pran" wrote:

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Pran" wrote:

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(vlookupformula0,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


"Pran" wrote:

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" wrote:

modify your vlookup formula to

=IF(vlookupformula0,vlookupformula,"")

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


"Pran" wrote:

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.

Hi,

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


=SUMPRODUCT(1*(LEN(D15:D19)0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Pran" wrote in message
...
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(vlookupformula0,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


"Pran" wrote:

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" wrote:

modify your vlookup formula to

=IF(vlookupformula0,vlookupformula,"")

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


"Pran" wrote:

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.

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

--
Rick (MVP - Excel)


"Ashish Mathur" wrote in message
...
Hi,

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


=SUMPRODUCT(1*(LEN(D15:D19)0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Pran" wrote in message
...
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(vlookupformula0,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


"Pran" wrote:

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" wrote:

modify your vlookup formula to

=IF(vlookupformula0,vlookupformula,"")

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


"Pran" wrote:

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.

Sure - that is right. Thank you

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rick Rothstein" wrote in message
...
I'm thinking you meant to say "...which have a length GREATER than 0".

--
Rick (MVP - Excel)


"Ashish Mathur" wrote in message
...
Hi,

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


=SUMPRODUCT(1*(LEN(D15:D19)0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Pran" wrote in message
...
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(vlookupformula0,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


"Pran" wrote:

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" wrote:

modify your vlookup formula to

=IF(vlookupformula0,vlookupformula,"")

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


"Pran" wrote:

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.