How to avoid 1/0/1900 0:00:00 while using a array formula combination of Min and IF

Question

Hi,

I am using a array formula combination to get the earliest start time for a give task in a give day.

Below is the formula that i am using.

{=MIN(IF('Raw Data'!$A$2:$A$37224=$B$2,IF(('Raw Data'!$F$2:$F$37224)>=($B5+$C$2),'Raw Data'!$F$2:$F$37224)))}

B2 is the Name of the Task

 B5 is date and C2 is the Time.

If a particular task is not found on a given date say B8 which is 12/30/2015 i get the value as 1/0/1900 0:00:00

How do i show this as blank? I have tried iferror and it did not work.

Regards,

Renato.

Answer 1

Mavericks334 wrote:

Thanks it did not work for me.

Did you see my critical errata?  IF(COUNTIFS(...), "", ...) should be IF(COUNTIFS(...) = 0, "", ...).  Sorry about the mistake.

Mavericks334 wrote:

Also for certain cases my formula if it does not find any value it is taking the next days value and displaying the same.

Yes, I noticed that in your second posting.  But it is unclear how that relates to the array-entered MIN formula.  You neglected to show any formulas; and the number of columns (4) does not match the MIN formula (A:F referenced).

I'm going to leave this discussion now; too busy.  I'm sure someone else will help you.

Answer 2

It worked when in used <= for the past dates however for the future dates it >=  works.

Also for certain cases my formula if it does not find any value it is taking the next days value and displaying the same.

Answer 3

Mavericks334 wrote:

{=MIN(IF('Raw Data'!$A$2:$A$37224=$B$2,IF(('Raw Data'!$F$2:$F$37224)>=($B5+$C$2),'Raw Data'!$F$2:$F$37224)))}

[....]

If a particular task is not found on a given date say B8 which is 12/30/2015 i get the value as 1/0/1900 0:00:00 [.]  How do i show this as blank?

It might be sufficient to use a Custom cell format, for example:

[=0]""; General

If you need help modifying your existing format, let us know what it is.

But if zero might be a legitimate result of the MIN expression, try the following (array-entered):

=IF(COUNTIFS('Raw Data'!$A$2:$A$37224,$B$2,'Raw Data'!$F$2:$F$37224,">="&$B5+$C$2) = 0, "",

MIN(IF('Raw Data'!$A$2:$A$37224=$B$2,IF('Raw Data'!$F$2:$F$37224>=$B5+$C$2,'Raw Data'!$F$2:$F$37224))))

[ERRATA] Added "= 0" to the formula.

Answer 4

Hi,

Sorry i am unable to do so. Below is sample screen shot.

Answer 5

Hi, could you upload in Onedrive a sample of your sheet