Returning a blank cell if any cell in my MIN formula is blank

pdledesma

New Member
Joined
Oct 23, 2014
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have searched, but haven't found the answer to my problem. Any help is appreciated.

Essentially, I have a table with date formulas. I want a final cell to pick the sooner of two date options (using a min formula). But if one of the date cells is blank, I want the final cell to be blank- right now ir treturns a date of 01/00/00.

Here is my table and formulas:

1644025024577.png


Here is the current formula in column H:

=MIN(IF(OR([@[Satellite ASD]]<>"",[@[Permanent Accumulation Start Date]]<>""),[@[Maximum Satellite Pickup Date]]),[@[Maximum Permanent Pickup Date]]).

Is there a way to leave the H:H cells blank if, let's say, the date in column D or F is missing? I am also appreciative if there is a cleaner way set up these formulas. If it helps, here are the formulas for cells in E and G too:

E:

=IF(B5=List!$A$6,List!$D$6,IF(B5=List!$A$7,List!$D$7,""))

G:

=IF(B5=List!$A$3,List!$D$3,IF(B5=List!$A$4,List!$D$4,IF(B5=List!$A$5,List!$D$5,IF(B5=List!$A$6,List!$D$3,IF(B5=List!$A$7,List!$D$4,IF(B5=List!$A$8,List!$D$8,IF(B5=List!$A$9,List!$D$9,IF(B5=List!$A$10,List!$D$10,""))))))))


Thank you in advance.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi pdledesma,
The only way I know how to do this is

Excel Formula:
=If(MIN(IF(OR([@[Satellite ASD]]<>"",[@[Permanent Accumulation Start Date]]<>""),[@[Maximum Satellite Pickup Date]]),[@[Maximum Permanent Pickup Date]]=0,"",=MIN(IF(OR([@[Satellite ASD]]<>"",[@[Permanent Accumulation Start Date]]<>""),[@[Maximum Satellite Pickup Date]]),[@[Maximum Permanent Pickup Date]])))

There are smarter people on this forum that probably have a cleaner way
 
Upvote 0
The only way I know how to do this is
That suggestion is not a valid formula syntax.

@pdledesma
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I am a little unsure if you want blank unless D and F are filled, or if you want blank unless all 4 are filled.

Try this if it is just D & F that need to be filled
Excel Formula:
=IF(COUNT([@[Satellite ASD]],[@[Permanent Accumulation Start Date]])=2,MIN(IF(OR([@[Satellite ASD]]<>"",[@[Permanent Accumulation Start Date]]<>""),[@[Maximum Satellite Pickup Date]]),[@[Maximum Permanent Pickup Date]]),"")

Or this if all 4 need to be filled (check your table name in this formula).
Excel Formula:
=IF(COUNT(Table1[@[Satellite ASD]:[Maximum Permanent Pickup Date]])=4,MIN(IF(OR([@[Satellite ASD]]<>"",[@[Permanent Accumulation Start Date]]<>""),[@[Maximum Satellite Pickup Date]]),[@[Maximum Permanent Pickup Date]]),"")
 
Last edited:
Upvote 0
That suggestion is not a valid formula syntax.

@pdledesma
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I am a little unsure if you want blank unless D and F are filled, or if you want blank unless all 4 are filled.

Try this if it is just D & F that need to be filled
Excel Formula:
=IF(COUNT([@[Satellite ASD]],[@[Permanent Accumulation Start Date]])=2,MIN(IF(OR([@[Satellite ASD]]<>"",[@[Permanent Accumulation Start Date]]<>""),[@[Maximum Satellite Pickup Date]]),[@[Maximum Permanent Pickup Date]]),"")

Or this if all 4 need to be filled (check your table name in this formula).
Excel Formula:
=IF(COUNT(Table1[@[Satellite ASD]:[Maximum Permanent Pickup Date]])=4,MIN(IF(OR([@[Satellite ASD]]<>"",[@[Permanent Accumulation Start Date]]<>""),[@[Maximum Satellite Pickup Date]]),[@[Maximum Permanent Pickup Date]]),"")

Mr. Peter_SSs,

Your assistance is greatly appreciated. The two field formula works as professed. However, it has proved an error in my description of what I want. It appears that I want H to be blank if BOTH D and F are blank and if only one of D or F are populated, I want to use the date from either E or G (I guess it would be the same if E and G were both blank, but it isn't important that all 4 are used since D and E are linked and so are F and G).

E is a date using D plus terms in a list in B (e.g., depending on what is selected in B, E = D + 90 or 180 or 365). Same for F and G.
 
Upvote 0
It appears that I want H to be blank if BOTH D and F are blank and if only one of D or F are populated,
Shouldn't that red "and" be "or"?

If H is not going to be blank, what is the actual calculation you want in that column?
 
Upvote 0
Shouldn't that red "and" be "or"?

If H is not going to be blank, what is the actual calculation you want in that column?

If H is not blank, I want it to show the earlier of the two dates in E or G, or if only E or G is populated, to simply show that date.

I was trying to not explain these details, but kind of knew it needed to be done... :)

This spreadsheet is to help facilities track/manage when hazardous waste needs to be picked up. The rules for waste storage are time- based (e.g., the clock starts when waste was put in a container) and vary depending on their waste generation status/ e.g., quantities generated [generally, LQG = 90 days, SQG = 180 days, exempt or universal waste is 365 days]). It would be easy if it was just that. HOWEVER, facilities can also employ something called "satellite" storage, which allows them to store a certain amount of waste at the point of generation for up to 365 days before moving the waste to their permanent storage area, where the above time frames would apply. I am trying to track the start dates in D and F and, based on the status identified in B, calculate the maximum storage dates in E and G.

The catch is that if satellite is used, the earlier of the two dates is the maximum storage date, which I want to capture in H. The tricky part is that some satellite facilities never move their waste and have it picked up directly from satellite. Many places do not use satellite at all. So while I want H to show the sooner of the two dates in E and G (or default to E OR G if only one is populated), I want H to be blank if D and F (or E and G) are blank.

Hoping this makes more sense.
 
Upvote 0
Still somewhat unclear to me but is this it?

pdledesma.xlsm
DEFGH
4Satellite ASDMaximum Satellite Pickup DatePermanent Accumulation Start DateMaximum Permanent Pickup DateMaximum Pickup Date?
51/07/20211/07/20224/07/202110/04/202110/04/2021
61/07/20211/07/20224/07/20211/07/2022
71/07/20214/07/20216/07/20216/07/2021
81/07/20224/07/202110/04/2021 
91/07/20211/07/202210/04/2021 
101/07/202210/04/2021 
Sheet1
Cell Formulas
RangeFormula
H5:H10H5=IF(COUNT([@[Satellite ASD]],[@[Permanent Accumulation Start Date]])=2,IF(COUNT([@[Maximum Satellite Pickup Date]],[@[Maximum Permanent Pickup Date]])=2,MIN([@[Maximum Satellite Pickup Date]],[@[Maximum Permanent Pickup Date]]),SUM([@[Maximum Satellite Pickup Date]],[@[Maximum Permanent Pickup Date]])),"")
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top