DATE question

nastech · 01-19-2006, 03:20 PM

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 · 01-19-2006, 04:10 PM

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" wrote:

> 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 · 01-19-2006, 05:40 PM

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 · 01-19-2006, 05:45 PM

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" wrote:

> 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
>
>

Roger Govier · 01-19-2006, 07:55 PM

Hi

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

--
Regards

Roger Govier

"nastech" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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 · 01-19-2006, 09:55 PM

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"))))

"Roger Govier" wrote:

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