DATE question

[nastech]

Hi, sorry for long question, trying to fix at 3 in the morning..
hope I give the right items:

cell with general format, text date? in form of :yymmdd (is proceeded with a
colon so if format drops would not loose proceeding zero).
(yymmdd needed to quick view/enter dates, sorting / visual scan many
records..)

:yymmdd followed by word text...
:060122 text then written...

$H$7 has 5 for 5 days

=IF(LEFT(T9,1)=":",IF(AND(TODAY()>DATE(MID(T9,2,2)+100,MID(T9,4,2),MID(T9,6,2)-$H$7)),"dd"),IF(BE9="T","dn",IF(BF9="T","up",IF(AK9<>"","ck",""))))

if use :060122 get correct dd response
if use :060130 get FALSE response, and rest of equation will not work.
if use : (no date) followed by text, get VALUE error


If I use the following equation:
=IF(LEFT(T9,1)<>":",IF(AND(TODAY()>DATE(MID(T9,2,2)+100,MID(T9,4,2),MID(T9,6,2)-$H$7)),"dd"),IF(BE9="T","dn",IF(BF9="T","up",IF(AK9<>"","ck",""))))

Rest of formula works, TODAY/DATE does not.

My best guesse I need to find an Exclusion that lets skip IF(AND(TODAY... so
it will not even look at it, items not to familiar with.. like text search:
if 1st 7 characters not equal :###### (colon 7 numbers, maybe a space)

I see the front part of my equation, as being incorrect: =IF(LEFT(T9,1)<>":",

Thanks in advance

 

[nastech]

correction update: formula works, within date works,
get FALSE if date in future of (5 days..); need to know how to make skip
false..

 

[nastech]

Formula Negation
How do I skip a formula, if left of cell does not equal a colon, followed by
7 numbers? thanks.

e.g.: date yymmdd
:060120

e.g.:
IF(LEFT(T9,7)= ??0000000),then do formula

 

[nastech]

Formula Negation
How do I skip a formula, if left of cell does not equal a colon, followed by
7 numbers? thanks.

e.g.: date yymmdd
:060120

e.g.:
IF(LEFT(T9,7)= ??0000000),then do formula

 

[Roger Govier]

Hi

Maybe
=IF(AND(LEFT(T9,7)=":",ISNUMBER(--MID(T9,2,6))),"do something","do
something else")

 

[nastech]

Hi, thanks for the reply, been typing on this trying to make it work. Was
looking at ISNUMBER also (what to the dashes before --MID do?) either way,
could not get that to work. (original: IF(T9=":" is used with a date in
column, at least :999999, all works; not a good answer yet though). updated
info here:

have 3 conditions:
1 < x days out, in-range (today> & apply "dd")
2 > x days out, then use sub formulas
3 cell empty, no date entered

currently working on / need help with something like:
=IF(LEFT(T9,1)=":", if date is :999999 all items work, no
date = FALSE error, status (1 & 2, not 3)
=IF(ISNUMBER(MID((T9,2,6)),
=IF(AND(LEFT(T9,1)=":",ISNUMBER(MID(T9,2,6))), not doing this right?
=IF(AND(LEFT(T9,1)=":",ISNUMBER(--MID(T9,2,6))), STATUS: (1 & 3, not 2)

details:
:yymmdd followed by word text... or:
:060122 text then written...

$H$7 has 5 for 5 days, I get correct response for
greater than & less than date & rest of formula works,
if no date is present, i get a FALSE, and rest of formula does not work.

=IF(LEFT(T9,1)=":",IF(AND(TODAY()>DATE(MID(T9,2,2)+100,MID(T9,4,2),MID(T9,6,2)-$H$7)),"dd",IF(BE9="T","dn",IF(BF9="T","up","ck"))))