text / formula help

N

nastech

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

(if can treat as a date in another cell: to add/subtract 5 days, would be
the 2nd question).

thanks
 
G

Govind

Hi,

Use

=DATE(MID(A1,6,2),MID(A1,4,2),MID(A1,2,2)+5)

where A1 has the number and text. I have assumed that the number
position is exactly as you described. The formula might change if not.

Regards

Govind.
 
N

nastech

That was Great!! Thanks, been trying to figure that out for awhile,
if I can ask, first: think you had values reversed, as I made work:
=DATE(MID(A1,2,2),MID(A1,4,2),MID(A1,6,2)+5)

verify will show if within 5 days, right? but how do I put in equation to
make use of
trying:
=IF(DATE(MID(C6,2,6)>DATE(MID(C6,2,2),MID(C6,4,2),MID(C6,6,2)+5,"Y","N")

does not quite work, am I missing a parenthesis? comma.. thanks..
 
N

nastech

Think I have it.. sorry for the repeat/ sloppy formula, something like:
salute.
=IF(TODAY()>DATE(MID(C6,2,2),MID(C6,4,2),MID(C6,6,2)+5),"yES")
 
G

Govind

Ok. great.Happy to help

Govind.
Think I have it.. sorry for the repeat/ sloppy formula, something like:
salute.
=IF(TODAY()>DATE(MID(C6,2,2),MID(C6,4,2),MID(C6,6,2)+5),"yES")

:
Click to expand...
 
N

nastech

Hi, if still there, cannot get the following to work, any ideas?

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

nastech

Hi, found Answer, thought you might like:
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")
 
Top