Formula with dates min max and a slight twist

[Jack in the UK]

Hi guys

="Licenced to:- " & MIN('22159'!D4:D5)
This wil return serial date need dddd mmmm yyyy, but =MIN('22159'!D4:D5) works just fine, ??? need a fix guys any ideas!

2

OK as above but if min is greater than 2 years old 742 days reads EXPIRED and the date or which is MIN, but the twist.

If both dates are older that 2 years need result as

Expired - oldest dats & other date

Will only be these two cells all is fixed i have MADE SURE

Cheers guys

 

[IML]

Hi Jack,
I think the first one can be settled with
="Licenced to:- " & TEXT(MIN('22159'!D4:D5),"dddd mmmm yyyy")

I'm not sure if I follow your second query. Could you throw in an example or two on that? If my son stays asleep, I'll take a look at it right away.

 

[Dave Patton]

For number 1, the following may help

="Licensed to:- "&TEXT(MIN(D4:D5),"dd-mmm-yyyy")

Part 2 and 3, I am not sure of the question.

 

[Jack in the UK]

OK try again, i have now to use an if statement so if older than two yeasr is EXPIRED or if not then Licenced.

=IF(TEXT(MIN('22159'!D3:D4),"dd-mmm-yyyy")<NOW()-(2*365.25),"Licensed to:- "&TEXT(MIN('22159'!D3:D4),"dd-mmm-yyyy"),"Expired on - :- "&TEXT(MIN('22159'!D3:D4),"dd-mmm-yyyy"))

this is fine.

Now the wist

If both are older than 2 years i need both in the result oldest first.

 

[IML]

Still not sure if that is what you're after, but give this a whirl:
=IF(MAX('22159'!D4:D5)<DATE(YEAR(NOW())-2,MONTH(NOW()),DAY(NOW())),TEXT(MIN('22159'!D4:D5),"dd-mm-yyyy & ")&TEXT(MAX('22159'!D4:D5),"dd-mm-yyyy"),"Licenced to:- " & TEXT(MIN('22159'!D4:D5),"dddd mmmm yyyy"))

If both dates are more than two years old, you get both dates, otherwise you get the result of the first formula.

Good luck

EDIT: disbaled HTML to show formula properly.
This message was edited by IML on 2002-08-24 10:23

 

[Dave Patton]

Please show an example.
one within your criteria and one
beyond the range.

Older than date in cell XX.

 

[Jack in the UK]

Whats happening??? these two loes to this formula??

XX =IF(TEXT(MIN('22159'!D3:D4),"dd-mmm-
XX yyyy")<NOW()-(2*365.25),"Licensed to:-XX "&TEXT(MIN('22159'!D3:D4),"dd-mmm-
XX yyyy"),"Expired on - :- "&TEXT(MIN('22159'!
XX D3:D4),"dd-mmm-yyyy"))

sorry guys remove the XX cant get this board to post correctly

 

[IML]

Jack
This is your post with HTML disable. Greater than and less than signs cause some strange voodoo type stuff. Also, I tried to answer up above.

On 2002-08-24 10:23, Jack in the UK wrote:
Whats happening??? these two loes to this formula??

XX =IF(TEXT(MIN('22159'!D3:D4),"dd-mmm-
XX yyyy")<NOW()-(2*365.25),"Licensed to:-XX "&TEXT(MIN('22159'!D3:D4),"dd-mmm-
XX yyyy"),"Expired on - :- "&TEXT(MIN('22159'!
XX D3:D4),"dd-mmm-yyyy"))

sorry guys remove the XX cant get this board to post correctly

 

[Jack in the UK]

Guys im cant possibly thanks you both enough, im right proper happy about thats' as i might say where i live.

Well done thank you so much.

I can use this which i will of cause.