Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE question
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(AK 9<"","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(AK 9<"","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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE question
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(AK 9<"","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(AK 9<"","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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE question
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE question
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(AK 9<"","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(AK 9<"","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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE question
Hi
Maybe =IF(AND(LEFT(T9,7)=":",ISNUMBER(--MID(T9,2,6))),"do something","do something else") -- Regards Roger Govier "nastech" wrote in message ... 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(AK 9<"","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(AK 9<"","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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE question
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 he 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Question | Excel Discussion (Misc queries) | |||
Date Question | Excel Discussion (Misc queries) | |||
Reference question | Excel Worksheet Functions | |||
Recurring annual events using a specific date as a trigger date | Excel Worksheet Functions | |||
Cell / Date Formatting Question when result = -0- | Excel Discussion (Misc queries) |