Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
text / formula help
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") |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
text / formula help
=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") |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
text / formula help
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(C 4,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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
text / formula help
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(C 4,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text to formula - Help pleaaase | Excel Discussion (Misc queries) | |||
text in formula explaining formula not to print | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Using the text from a cell as a range name in a formula | Excel Discussion (Misc queries) | |||
Concatenation formula loses text wrap formatting | Excel Discussion (Misc queries) |