database and date

[puiuluipui]

Hi, i want to extract some data from another sheet (or the same sheet), with
"if" i think.....and macro maybe. In database example, i have a
planning...john's day is monday, and mary's day it's friday. in example 1, i
want to write the day when john and mary will arrive and in "c" to see if the
date it's in planning or not. If they are comming in the correct day or not.
The problem, how i see it, is that i write a date in example 1 and a day in
the database. It will have to convert somehow the day to date, from the
current date, because the date will change and the formula to know this. I
hope my example will explain better than i do...... thanks in advance.



Example 1: (this is how i would like it)
a b c

john 12.05.2008 NO
mary 16.05.2008 YES



Database example 2:

a b
john monday
mary friday

 

[Stefi]

I'm not sure that I fully understood the job, but try this formula in Example
1 column C:

=IF(VLOOKUP(A2,Example2!A:B,2,FALSE)=TEXT(B2,"dddd"),"YES","NO")

where Example2 is the sheet name of Database example 2.

By the way, 12.05.2008 is Monday!
Use capital initials in Database example 2:
Monday
Friday
because TEXT(B2,"dddd") returns the correct English format!

Regards,
Stefi

„puiuluipui†ezt írta:

 

[puiuluipui]

Hi Stefi...thanks, but i can't make it work. I will try to explain
better...if you have a few minutes for me.
So…i will use only one sheet. I think it,s easy.I have the database (e and
f) in the same sheet. In database (e,f) the date will be monday and friday,
and in the table it will be a date(11.05.2008....) I want the formula to find
in database if John will come in the correct day. But i dont know how to
format the days in the database, because it can only be monday, friday...
If John will come on 11.05.2008, the formula to see that John's planning day
it's monday and monday it's not 11.05.2008, and to display NO.
i hope it will be easy with the database in the same window, and thanks
very, very much for your time.
p.s. capital initials......you mean the first letter to be with caps?
(Monday)?
sorry, but my english.....
thanks again...

This is how i want the table to be.

a b c
e f

1 John 11.05.2008 NO John
Monday
2 Mary 16.05.2008 YES Mary
Friday
3 John 19.05.2008 YES
4 Mary 20.05.2008 NO


"Stefi" a scris:

 

[Stefi]

a b c e f
John 11.05.2008 NO John Monday
Mary 16.05.2008 YES Mary Friday

Column B contains dates, column F contains plain texts (Monday, Friday, etc.)
The formula in column C:
=IF(VLOOKUP(A2,E:F,2,FALSE)=TEXT(B2,"dddd"),"YES","NO")

The formula does exactly what you said. Please post details if it doesn'work
for you!

Regards,
Stefi

„puiuluipui†ezt írta:

 

[puiuluipui]

Hi Stefi, my excel show, after copy/paste, the formula but only as text. So,
i replaced (, with ...
your formula
=IF(VLOOKUP(A2,E:F,2,FALSE)=TEXT(B2,"dddd"),"YES","NO")
new one
=IF(VLOOKUP(A1;E:F;2;FALSE)=TEXT(B1;"dddd");"YES";"NO")
I was happy, it work, but it show only "NO". Than i replaced YES with NO,
like this
=IF(VLOOKUP(A2;E:F;2;FALSE)=TEXT(B2;"dddd");"NO";"YES")
But it show me only YES, nomatter what the date is.
I dont know why it works with (. But i guess the formula must work the way
you wrote it. Maybe my excel 2003 it's setup in different way, but i dont
know how.
I formated both dates columns as date (English United States) and i wrote
everything in diferent ways...
Monday
MONDAY
monday......
Should i format database or something in some way? or your formula it's
enough?


Maybe you can figure it out.

THANKS!






"Stefi" a scris:

 

[Stefi]

I guess that you use a national language Excel version. English version uses
comma (,) as argument separator, some national language versions (mostly
Europeans, like my Hungarian version) use semicolon (. In this forum
formulae are normally shown converted to English format, so you always have
to convert them to your national language format.
National language Excel versions also use different format codes, so "dddd"
(d stands for DAY) is to be changed to the first letter of the word meaning
day in your language. E.g. in Hungarian I have to use "nnnn" (Nap=Day). Day
names (Monday, Friday, ...) are also to be changed to their national language
equivalents.

Regards,
Stefi




„puiuluipui†ezt írta:

 

[puiuluipui]

i used "dddd" but i have changed the days name. thanks allot. you are great.
thanks again.....

"Stefi" a scris:

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„puiuluipui†ezt írta: