ExcelBanter - If Date Null Show Nothing

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - If Date Null Show Nothing (https://www.excelbanter.com/excel-worksheet-functions/127713-if-date-null-show-nothing.html)

Ardy

If Date Null Show Nothing

Hello All:
I am Using The =MAX(A1:A5) to pick the most recent date from the given
range. The Problem I am having is that if the Range is Null no date I
get the Date 1/0/1900, Is there any way we can have it not show any
dates if the range is null. I had the similar problem with calculating
percentiles and I tried to adopt variations of that to this but not
working

Ardy

If Date Null Show Nothing

try
=if max(a1:a5)=0,"",Max(a1:a5))

"Ardy" wrote:

Hello All:
I am Using The =MAX(A1:A5) to pick the most recent date from the given
range. The Problem I am having is that if the Range is Null no date I
get the Date 1/0/1900, Is there any way we can have it not show any
dates if the range is null. I had the similar problem with calculating
percentiles and I tried to adopt variations of that to this but not
working

Ardy

driller

If Date Null Show Nothing

Is there any way we can have it not show any
dates if the range is null.

maybe a slight if formula will NOT SHOW ..
=IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5))

--
*****
birds of the same feather flock together..

"bj" wrote:

try
=if max(a1:a5)=0,"",Max(a1:a5))

"Ardy" wrote:

Hello All:
I am Using The =MAX(A1:A5) to pick the most recent date from the given
range. The Problem I am having is that if the Range is Null no date I
get the Date 1/0/1900, Is there any way we can have it not show any
dates if the range is null. I had the similar problem with calculating
percentiles and I tried to adopt variations of that to this but not
working

Ardy

Ardy

If Date Null Show Nothing

I Tried =if max(B7:B10)=0,"",Max(B7:B10)) it gives me an error
(B7:B10) is the actual Range,

I tried =IF(ISERROR(MAX(B7:B10)),"",MAX(B7:B10)) No errir but the date
still stays at 1/0/1900

Ardy

On Jan 25, 8:08 am, driller wrote:
Is there any way we can have it not show any

dates if the range is null.maybe a slight if formula will NOT SHOW ..
=IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5))

--
*****
birds of the same feather flock together..

"bj" wrote:
try
=if max(a1:a5)=0,"",Max(a1:a5))

"Ardy" wrote:

Hello All:
I am Using The =MAX(A1:A5) to pick the most recent date from the given
range. The Problem I am having is that if the Range is Null no date I
get the Date 1/0/1900, Is there any way we can have it not show any
dates if the range is null. I had the similar problem with calculating
percentiles and I tried to adopt variations of that to this but not
working

Ardy

Pete_UK

If Date Null Show Nothing

BJ left a bracket out of his formula - try this:

=IF(MAX(B7:B10)=0,"",MAX(B7:B10))

The other formula (with ISERROR) will not work, as the MAX function
will not return an error in your situation - it will return zero, which
will get displayed as 1/0/1900.

Hope this helps.

Pete

On Jan 25, 5:17 pm, "Ardy" wrote:
I Tried =if max(B7:B10)=0,"",Max(B7:B10)) it gives me an error
(B7:B10) is the actual Range,

I tried =IF(ISERROR(MAX(B7:B10)),"",MAX(B7:B10)) No errir but the date
still stays at 1/0/1900

Ardy

On Jan 25, 8:08 am, driller wrote:

Is there any way we can have it not show any

dates if the range is null.maybe a slight if formula will NOT SHOW ..
=IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5))

--
*****
birds of the same feather flock together..

"bj" wrote:
try
=if max(a1:a5)=0,"",Max(a1:a5))

"Ardy" wrote:

Hello All:
I am Using The =MAX(A1:A5) to pick the most recent date from the given
range. The Problem I am having is that if the Range is Null no date I
get the Date 1/0/1900, Is there any way we can have it not show any
dates if the range is null. I had the similar problem with calculating
percentiles and I tried to adopt variations of that to this but not
working

Ardy- Hide quoted text -- Show quoted text -

driller

If Date Null Show Nothing

It is right to assume that B7:B10 will not return an error result ONLY if
such are made of hard data - not a result from a series of formulas,,,
to be rest assured in getting rid of any NULL and 1/0/1900

just adjust a little more with this formula

=IF(or(ISERROR(MAX(B7:B10)),MAX(B7:B10)=0),"",MAX( B7:B10))

--
*****
birds of the same feather flock together..

"Pete_UK" wrote:

BJ left a bracket out of his formula - try this:

=IF(MAX(B7:B10)=0,"",MAX(B7:B10))

The other formula (with ISERROR) will not work, as the MAX function
will not return an error in your situation - it will return zero, which
will get displayed as 1/0/1900.

Hope this helps.

Pete

On Jan 25, 5:17 pm, "Ardy" wrote:
I Tried =if max(B7:B10)=0,"",Max(B7:B10)) it gives me an error
(B7:B10) is the actual Range,

I tried =IF(ISERROR(MAX(B7:B10)),"",MAX(B7:B10)) No errir but the date
still stays at 1/0/1900

Ardy

On Jan 25, 8:08 am, driller wrote:

Is there any way we can have it not show any

dates if the range is null.maybe a slight if formula will NOT SHOW ..
=IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5))

--
*****
birds of the same feather flock together..

"bj" wrote:
try
=if max(a1:a5)=0,"",Max(a1:a5))

"Ardy" wrote:

Hello All:
I am Using The =MAX(A1:A5) to pick the most recent date from the given
range. The Problem I am having is that if the Range is Null no date I
get the Date 1/0/1900, Is there any way we can have it not show any
dates if the range is null. I had the similar problem with calculating
percentiles and I tried to adopt variations of that to this but not
working

Ardy- Hide quoted text -- Show quoted text -

Ardy

If Date Null Show Nothing

Thanks Guys Works Like A Charm
Ardy

On Jan 25, 9:33 am, "Pete_UK" wrote:
BJ left a bracket out of his formula - try this:

=IF(MAX(B7:B10)=0,"",MAX(B7:B10))

The other formula (with ISERROR) will not work, as the MAX function
will not return an error in your situation - it will return zero, which
will get displayed as 1/0/1900.

Hope this helps.

Pete

On Jan 25, 5:17 pm, "Ardy" wrote:

I Tried =if max(B7:B10)=0,"",Max(B7:B10)) it gives me an error
(B7:B10) is the actual Range,

I tried =IF(ISERROR(MAX(B7:B10)),"",MAX(B7:B10)) No errir but the date
still stays at 1/0/1900

Ardy

On Jan 25, 8:08 am, driller wrote:

Is there any way we can have it not show any

dates if the range is null.maybe a slight if formula will NOT SHOW ..
=IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5))

--
*****
birds of the same feather flock together..

"bj" wrote:
try
=if max(a1:a5)=0,"",Max(a1:a5))

"Ardy" wrote:

Hello All:
I am Using The =MAX(A1:A5) to pick the most recent date from the given
range. The Problem I am having is that if the Range is Null no date I
get the Date 1/0/1900, Is there any way we can have it not show any
dates if the range is null. I had the similar problem with calculating
percentiles and I tried to adopt variations of that to this but not
working

Ardy- Hide quoted text -- Show quoted text -

Ardy

If Date Null Show Nothing

I changed it to drillers's version since it covers the OR option just
in case.

Ardy

On Jan 25, 10:01 am, driller wrote:
It is right to assume that B7:B10 will not return an error result ONLY if
such are made of hard data - not a result from a series of formulas,,,
to be rest assured in getting rid of any NULL and 1/0/1900

just adjust a little more with this formula

=IF(or(ISERROR(MAX(B7:B10)),MAX(B7:B10)=0),"",MAX( B7:B10))

--
*****
birds of the same feather flock together..

"Pete_UK" wrote:
BJ left a bracket out of his formula - try this:

=IF(MAX(B7:B10)=0,"",MAX(B7:B10))

The other formula (with ISERROR) will not work, as the MAX function
will not return an error in your situation - it will return zero, which
will get displayed as 1/0/1900.

Hope this helps.

Pete

On Jan 25, 5:17 pm, "Ardy" wrote:
I Tried =if max(B7:B10)=0,"",Max(B7:B10)) it gives me an error
(B7:B10) is the actual Range,

I tried =IF(ISERROR(MAX(B7:B10)),"",MAX(B7:B10)) No errir but the date
still stays at 1/0/1900

Ardy

On Jan 25, 8:08 am, driller wrote:

Is there any way we can have it not show any

dates if the range is null.maybe a slight if formula will NOT SHOW ..
=IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5))

--
*****
birds of the same feather flock together..

"bj" wrote:
try
=if max(a1:a5)=0,"",Max(a1:a5))

"Ardy" wrote:

Hello All:
I am Using The =MAX(A1:A5) to pick the most recent date from the given
range. The Problem I am having is that if the Range is Null no date I
get the Date 1/0/1900, Is there any way we can have it not show any
dates if the range is null. I had the similar problem with calculating
percentiles and I tried to adopt variations of that to this but not
working

Ardy- Hide quoted text -- Show quoted text -

All times are GMT +1. The time now is 02:40 AM.