Warning with near date

[Guest]

Hi!
I used the next formula:
=IF(OR(X16="x";X16="X");"";IF(AND(TODAY()<(DATEVALUE(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

 

[Guest]

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",""),"")

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

"Truck formula" wasn't a critic, by contrary
And for my needs, there's no problem cos i can put that formula in a col
hiden and so the cell i wan't will read the values as i wan't. It was a great
job, wich i only have to thanks all time and patience given to this problem.
I've tested the formula part by part and theres a piece that i can't solve
"DATEVALUE(MATCH("F",$B2:$AF2,0)"
it gives me error "value", i've tryed to solve but all my moves were lost.
About VBA, i was trying to avoid, because has already some VBA, but if there
are no other way...
Can i email u? And send the file to undearstand better the problem? Beacuse
is not easy to me explain the complete table.
My mail is (e-mail address removed)
Thanks again.