text / formula help

[nastech]

cannot get the following equation to work, is it wrong?
only way to get to switch TRUE to FALSE, is to flip > to <; Changing the
days in A4 having no effect.

=IF(TODAY()>DATE(MID(C4,2,2),MID(C4,4,2),MID(C4,6,2)-$A$4),"yes","no")

details:
am trying to find a number within text, to do a calculation on it. Item is as:
:060120 at the left of a cell, with text following.

(need to treat as a date in another cell: to See if within future range of
x days)
A4 has: 5
B4 has: =TODAY()
C4 has:
:060122

D4 has:
=DATE(MID(C4,2,2),MID(C4,4,2),MID(C4,6,2)-$A$4) seems to work

not working:
=IF(TODAY()>DATE(MID(C4,2,2),MID(C4,4,2),MID(C4,6,2)-$A$4),"yes","no")

[paul]

=TODAY()>DATE(MID(C4,2,2),MID(C4,4,2),MID(C4,6,2)-$A$4) will reveal if you
are actually comparing dates.If you dont get true when you know you should
then excel isnt seeing the numbers/dates/text as you think!

paul
remove nospam for email addy!



"nastech" wrote:

> cannot get the following equation to work, is it wrong?
> only way to get to switch TRUE to FALSE, is to flip > to <; Changing the
> days in A4 having no effect.
>
> =IF(TODAY()>DATE(MID(C4,2,2),MID(C4,4,2),MID(C4,6,2)-$A$4),"yes","no")
>
> details:
> am trying to find a number within text, to do a calculation on it. Item is as:
> :060120 at the left of a cell, with text following.
>
> (need to treat as a date in another cell: to See if within future range of
> x days)
> A4 has: 5
> B4 has: =TODAY()
> C4 has:
> :060122
>
> D4 has:
> =DATE(MID(C4,2,2),MID(C4,4,2),MID(C4,6,2)-$A$4) seems to work
>
> not working:
> =IF(TODAY()>DATE(MID(C4,2,2),MID(C4,4,2),MID(C4,6,2)-$A$4),"yes","no")
>

[Dav]

If you format the cell with the date expression that works in to show the year as 4 figures you will see it is 1906 not 2006 as you probably think, the logic works but the date is not the date you think it is, if in the year bit of the formula you add +100 it should work as you want

=IF(TODAY()>DATE(MID(C4,2,2)+100,MID(C4,4,2),MID(C4,6, 2)-$A$4),"yes","no")

cheer

Dav

[nastech]

You the man!, did get the equation from someone else, so was 2 parter... knew
would be a tuff question, just like couldn't find how to "fix" a cell for
6mo/year..
(after cond. format, accidental click in external cell see $$$, hit help...
was pretty ugly), don't know why they don't have preset format for old
database types see sorting by yymmdd, .... anyways now still working on
figuring out web queries, can see that is going to be a pain, it's not just
listed somewhere how to simply do it. THANKS .!!!!

"Dav" wrote:

>
> If you format the cell with the date expression that works in to show
> the year as 4 figures you will see it is 1906 not 2006 as you probably
> think, the logic works but the date is not the date you think it is, if
> in the year bit of the formula you add +100 it should work as you want
>
> =IF(TODAY()>DATE(MID(C4,2,2)+100,MID(C4,4,2),MID(C4,6,
> 2)-$A$4),"yes","no")
>
> cheer
>
> Dav
>
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> View this thread: http://www.excelforum.com/showthread...hreadid=502366
>
>