 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008database and date
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 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: > 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 [quoted text clipped - 17 lines] > john monday > mary friday 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: > I'm not sure that I fully understood the job, but try this formula in Example > 1 column C: [quoted text clipped - 35 lines] > > john monday > > mary friday 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: > Hi Stefi...thanks, but i can't make it work. I will try to explain > better...if you have a few minutes for me. [quoted text clipped - 65 lines] > > > john monday > > > mary friday 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: > a b c e f > John 11.05.2008 NO John Monday [quoted text clipped - 81 lines] > > > > john monday > > > > mary friday 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: > Hi Stefi, my excel show, after copy/paste, the formula but only as text. So, > i replaced (, with ;)... [quoted text clipped - 108 lines] > > > > > john monday > > > > > mary friday i used "dddd" but i have changed the days name. thanks allot. you are great. thanks again..... "Stefi" a scris: > I guess that you use a national language Excel version. English version uses > comma (,) as argument separator, some national language versions (mostly [quoted text clipped - 124 lines] > > > > > > john monday > > > > > > mary friday You are welcome! Thanks for the feedback! Stefi „puiuluipui” ezt írta: > i used "dddd" but i have changed the days name. thanks allot. you are great. > thanks again..... [quoted text clipped - 129 lines] > > > > > > > john monday > > > > > > > mary friday |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.