|
Warning with near date
Hi!
I used the next formula: =IF(OR(X16="x";X16="X");"";IF(AND(TODAY()<(DATEVAL UE(N16&"/"&$N$4&"/"&V16)-7);(DATEVALUE(N16&"/"&$N$4&"/"&V16)));"";"Incoming colect day")) in a kind of calendar in wich i had a table like: A All Months(J to U) Year x (missing days) (day) 2007(f.e.) x it worked just fine. but now I have a diferent table with diferent properties, i.e. Jan - 1 2 3 4 5 6 7 8 F F F F F F 15 16 17 18 ... 2007 Feb - 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17... 2007 Mar - .... Apr - .... May - 1 2 3 4 M M M M 9 10 11 12 13... 2007 The "(M) Marked days" and "(F) Vacancies" that with a conditional format are putted with color, i've doe this good. but now I want to have a cell in wich ill have a warning like "near Vacancies in 5 days" that appears on 5 days before vacancies (M). Can someone help me? Thanks |
Warning with near date
Assumptions:
1. Col A is Month (Jan etc) starting row 2 2.Cols B to AF are days of month (1 to 31) 3. Col AG is Year 4. There is only one set of vacancies in a month: if not, I think VBA is required. 5. We test for "F" not "M" as per your posting (or did I misunderstand?) In AH2 and copy down =IF(ISNUMBER(MATCH("F",$B2:$AF2,0)),IF(AND(TODAY() +5=DATEVALUE(MATCH("F",$B2:$AF2,0)& "/" & $A2 &"/" & $AG2),DATEVALUE(MATCH("F",$B2:$AF2,0)& "/" & $A2 &"/" & $AG2)TODAY()),"Vacancies in 5 days",""),"") "Micos3" wrote: Hi! I used the next formula: =IF(OR(X16="x";X16="X");"";IF(AND(TODAY()<(DATEVAL UE(N16&"/"&$N$4&"/"&V16)-7);(DATEVALUE(N16&"/"&$N$4&"/"&V16)));"";"Incoming colect day")) in a kind of calendar in wich i had a table like: A All Months(J to U) Year x (missing days) (day) 2007(f.e.) x it worked just fine. but now I have a diferent table with diferent properties, i.e. Jan - 1 2 3 4 5 6 7 8 F F F F F F 15 16 17 18 ... 2007 Feb - 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17... 2007 Mar - .... Apr - .... May - 1 2 3 4 M M M M 9 10 11 12 13... 2007 The "(M) Marked days" and "(F) Vacancies" that with a conditional format are putted with color, i've doe this good. but now I want to have a cell in wich ill have a warning like "near Vacancies in 5 days" that appears on 5 days before vacancies (M). Can someone help me? Thanks |
Warning with near date
UUAAUUU!!!
Like a friend of mine says "is a formula truck!!!" I'm trying to make adjustments to fit in my case, because i'm having problems in my excell, and the formula had error, but i guess is a non recognizement of some expression formula. I'll try to adapt to other expression. Great Job!!! Thanks is not enough!!! Many thanks :) "Toppers" wrote: Assumptions: 1. Col A is Month (Jan etc) starting row 2 2.Cols B to AF are days of month (1 to 31) 3. Col AG is Year 4. There is only one set of vacancies in a month: if not, I think VBA is required. 5. We test for "F" not "M" as per your posting (or did I misunderstand?) In AH2 and copy down =IF(ISNUMBER(MATCH("F",$B2:$AF2,0)),IF(AND(TODAY() +5=DATEVALUE(MATCH("F",$B2:$AF2,0)& "/" & $A2 &"/" & $AG2),DATEVALUE(MATCH("F",$B2:$AF2,0)& "/" & $A2 &"/" & $AG2)TODAY()),"Vacancies in 5 days",""),"") "Micos3" wrote: Hi! I used the next formula: =IF(OR(X16="x";X16="X");"";IF(AND(TODAY()<(DATEVAL UE(N16&"/"&$N$4&"/"&V16)-7);(DATEVALUE(N16&"/"&$N$4&"/"&V16)));"";"Incoming colect day")) in a kind of calendar in wich i had a table like: A All Months(J to U) Year x (missing days) (day) 2007(f.e.) x it worked just fine. but now I have a diferent table with diferent properties, i.e. Jan - 1 2 3 4 5 6 7 8 F F F F F F 15 16 17 18 ... 2007 Feb - 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17... 2007 Mar - .... Apr - .... May - 1 2 3 4 M M M M 9 10 11 12 13... 2007 The "(M) Marked days" and "(F) Vacancies" that with a conditional format are putted with color, i've doe this good. but now I want to have a cell in wich ill have a warning like "near Vacancies in 5 days" that appears on 5 days before vacancies (M). Can someone help me? Thanks |
Warning with near date
Sorry about the "truck" but what you require is easy to say but slightly more
complex to put in a formula: particularly given the format of your data. And given my caveats, the formula isn't "perfect". If you want to send me a sample w/sheet, I'll look to see if there are other solutions. Is VBA an acceptable solution? "Micos3" wrote: UUAAUUU!!! Like a friend of mine says "is a formula truck!!!" I'm trying to make adjustments to fit in my case, because i'm having problems in my excell, and the formula had error, but i guess is a non recognizement of some expression formula. I'll try to adapt to other expression. Great Job!!! Thanks is not enough!!! Many thanks :) "Toppers" wrote: Assumptions: 1. Col A is Month (Jan etc) starting row 2 2.Cols B to AF are days of month (1 to 31) 3. Col AG is Year 4. There is only one set of vacancies in a month: if not, I think VBA is required. 5. We test for "F" not "M" as per your posting (or did I misunderstand?) In AH2 and copy down =IF(ISNUMBER(MATCH("F",$B2:$AF2,0)),IF(AND(TODAY() +5=DATEVALUE(MATCH("F",$B2:$AF2,0)& "/" & $A2 &"/" & $AG2),DATEVALUE(MATCH("F",$B2:$AF2,0)& "/" & $A2 &"/" & $AG2)TODAY()),"Vacancies in 5 days",""),"") "Micos3" wrote: Hi! I used the next formula: =IF(OR(X16="x";X16="X");"";IF(AND(TODAY()<(DATEVAL UE(N16&"/"&$N$4&"/"&V16)-7);(DATEVALUE(N16&"/"&$N$4&"/"&V16)));"";"Incoming colect day")) in a kind of calendar in wich i had a table like: A All Months(J to U) Year x (missing days) (day) 2007(f.e.) x it worked just fine. but now I have a diferent table with diferent properties, i.e. Jan - 1 2 3 4 5 6 7 8 F F F F F F 15 16 17 18 ... 2007 Feb - 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17... 2007 Mar - .... Apr - .... May - 1 2 3 4 M M M M 9 10 11 12 13... 2007 The "(M) Marked days" and "(F) Vacancies" that with a conditional format are putted with color, i've doe this good. but now I want to have a cell in wich ill have a warning like "near Vacancies in 5 days" that appears on 5 days before vacancies (M). Can someone help me? Thanks |
| All times are GMT +1. The time now is 10:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com