IF AND Statement for dates

[jarett]

I have 3 columns of dates, I want to display the oldest date in a new column "RUSH DATE". When the date column is null from the data source it populates the cell with the value "-53688". How can I get those cells to display a blank value when they are "null" or can I account for this value in the formula to get my result? In the example on line 35 I would want the rush date value to be "3/24/2023" . I couldnt get the XL2BB to work.

 

[Fluff]

Maybe

=MINIFS(H2:J2,H2:J2,">0")

 

[jarett]

Maybe

=MINIFS(H2:J2,H2:J2,">0")

So it displays the correct date if there are more than one valid dates in the other 3 columns, however when the value is "-53688" which is the DB's null the result in the new column is "1/0/1900". How can this display a blank cell instead of the 1/0/1900?

 

[Fluff]

How about

=iferror(1/(1/0MINIFS(H2:J2,H2:J2,">0")),"")

 

[Joe4]

=iferror(1/(1/0MINIFS(H2:J2,H2:J2,">0")),"")

Fluff, you have a typo in that formula. Did you mean:

=iferror(1/(1/MINIFS(H2:J2,H2:J2,">0")),"")

(remove the 0 from before the MINIFS?)

 

[Fluff]

You're quite right, not sure where that crept in.

 

[jarett]

Worked great and used it as an outline for another column, one more question. Trying to calculate number of days between two columns, one columns contains a formula "O"

=IFERROR(1/(1/MINIFS(P3,P3,">0")),"")

the other column is just a date "M". I want the time between those two days, I got it to calculate if there is a vaild value in the column O "DATE COMPLETED", just when that column has no value and just the formula I get a #VALUE! error in my WIP TIME column, this is the formula in the WIP column

=IF(ISBLANK([@[DATE COMPLETED]]),"",NETWORKDAYS([@[UDF_DATE_PRODUCTION_RECEIVED]],[@[DATE COMPLETED]]))

 

[Fluff]

You need to use

=IF([@[DATE COMPLETED]]="","",NETWORKDAYS([@[UDF_DATE_PRODUCTION_RECEIVED]],[@[DATE COMPLETED]]))

a cell that contains a formula is not blank

 

[jarett]

You need to use

=IF([@[DATE COMPLETED]]="","",NETWORKDAYS([@[UDF_DATE_PRODUCTION_RECEIVED]],[@[DATE COMPLETED]]))

a cell that contains a formula is not blank

Prefect!

 

[Fluff]

You're welcome & thanks for the feedback.