Min if

[acellier]

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

 

[jtakw]

Hi,

Add an IF test:

Book1
	L	M	N
6	2/21/2018		1/1/2018
7
8	2/23/2018
9	2/24/2018
10	2/25/2018
11	2/26/2018
12	2/27/2018
13	2/28/2018
14	1/1/2018
Sheet3

Cell Formulas
Range	Formula
N6	=IF(MIN(L6:L55)=0,"",MIN(L6:L55))

 

[acellier]

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

 

[acellier]

never mind. Figured that out

 

[jtakw]

You're welcome, welcome to the forum, glad you have it working.

 

[acellier]

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.

 

[jtakw]

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
	L	M	N
6	2/21/2018		12/31/2017
7
8	2/23/2018
9	2/24/2018
10	2/25/2018
11	2/26/2018
12	2/27/2018
13	2/28/2018
14	1/1/2018
Sheet3

Cell Formulas
Range	Formula
N6	=IF(MIN(L6:L55)=0,"",MIN(L6:L55)-1)

 

[acellier]

ahh. had my ) in the wrong spot. Thank you very much!!