Min if

acellier

New Member
Joined
Feb 21, 2018
Messages
5
Hello All,

I have a work spreadsheet. I am searching for the minimum date in a column, but if there are no dates entered I want it to stay blank. So formula for min is =min(L6:L55). But when there are no dates entered it defaults to 1/00/00 when I want it to just stay blank.

any help would be appreciated.

thanks
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi,

Add an IF test:


Book1
LMN
62/21/20181/1/2018
7
82/23/2018
92/24/2018
102/25/2018
112/26/2018
122/27/2018
132/28/2018
141/1/2018
Sheet3
Cell Formulas
RangeFormula
N6=IF(MIN(L6:L55)=0,"",MIN(L6:L55))
 
Last edited:
Upvote 0
Thank you!! Decided I want the minium dates -1. Do you have any advice on how to do that? So shortest date is 1/30 I want it read 1/29. If there are no dates read nothing
 
Upvote 0
You're welcome, welcome to the forum, glad you have it working.
 
Upvote 0
actually... embarassed to say I dont. I thought I had it by just putting -1 at the end of the formula, but when I do that it comes up #value when there is nothing in the column. Works when there are dates, but I could just use Min(L6:L55)-1, but when there is nothing thats when I want it to show nothing.
 
Upvote 0
actually... embarassed to say I dont. I thought I had it by just putting -1 at the end of the formula, but when I do that it comes up #value when there is nothing in the column. Works when there are dates, but I could just use Min(L6:L55)-1, but when there is nothing thats when I want it to show nothing.

You probably have the -1 misplaced in the formula, try this:


Book1
LMN
62/21/201812/31/2017
7
82/23/2018
92/24/2018
102/25/2018
112/26/2018
122/27/2018
132/28/2018
141/1/2018
Sheet3
Cell Formulas
RangeFormula
N6=IF(MIN(L6:L55)=0,"",MIN(L6:L55)-1)
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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