 |
 | 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 / New Users / January 2008Adding Days that are in the form of Text eg monday, tuesday
Hi i was hoping someone could help what i have is column "A" which has a list of days in the form monday, wednesday etc. Then i want column B to display the next day. A B 1 Day Day+1 2 Day Day+1 3 Day Day+1 Column A is day stock will run out Column B is the day after I have done something similar to this before but I can't think how Hope someone can help Thanks For your help Katy =LOOKUP(A1,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"})  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi i was hoping someone could help > [quoted text clipped - 16 lines] > > Katy > =LOOKUP(A1, > {"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday", > "Saturday"}, > {"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday", > "Sunday"}) I like that form, but it does not work for me. I am not surprised. The Help page states that the values must be in ascending order. So wouldn't it need to be: =lookup(A1, {"Friday","Monday","Saturday","Sunday","Thursday","Tuesday", "Wednesday"}, {"Saturday","Tuesday","Sunday","Monday","Friday","Wednesday", "Thursday"}) Could also just as well be: =HLOOKUP(A1,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Sa turday";"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunda y"},2,0) SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- On Jan 2, 3:12 pm, "Bob Phillips" <bob....@somewhere.com> wrote: > =LOOKUP(A1, > {"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday", > "Saturday"}, > {"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday", > "Sunday"}) I like that form, but it does not work for me. I am not surprised. The Help page states that the values must be in ascending order. So wouldn't it need to be: =lookup(A1, {"Friday","Monday","Saturday","Sunday","Thursday","Tuesday", "Wednesday"}, {"Saturday","Tuesday","Sunday","Monday","Friday","Wednesday", "Thursday"}) Yes of course. I only tested on a few days and it worked. Foolish me! Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) On Jan 2, 3:12 pm, "Bob Phillips" <bob....@somewhere.com> wrote: > =LOOKUP(A1, > {"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday", > "Saturday"}, > {"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday", > "Sunday"}) I like that form, but it does not work for me. I am not surprised. The Help page states that the values must be in ascending order. So wouldn't it need to be: =lookup(A1, {"Friday","Monday","Saturday","Sunday","Thursday","Tuesday", "Wednesday"}, {"Saturday","Tuesday","Sunday","Monday","Friday","Wednesday", "Thursday"}) > what i have is column "A" which has a list of days in the form > monday, wednesday etc. Then i want column B to display the > next day. If you truly have the text "Monday" etc, I would set up a table somewhere (could be on another sheet), then put the following in B1 (for example): =vlookup(A1, Sheet2!$A$1:$B$7, 2, false) where A1:B7 on Sheet2 has (forgive any alignment problems): Monday =A2 Tuesday =A3 Wednesday =A4 Thursday =A5 Friday =A6 Saturday =A7 Sunday =A1 > Column A is day stock will run out > Column B is the day after So it would seem likely to me that column A contains a date, and you might have used a custom format to show only the day of the week; or perhaps column A contains a formula that returns the day of the week based on a date (e.g. =WEEKDAY(C1)). If either is the case, I think it would be better to use 1+WEEKDAY(theDate), however you determine "theDate". With A1 containing a day name (eg Tuesday) Maybe this (in sections for readability: =INDEX({"Tues","Wednes","Thurs","Fri","Satur","Sun","Mon"}, (SEARCH(LEFT(A1,2),"MoTuWeThFrSaSu")-1)/2+1)&"day" Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Hi i was hoping someone could help > [quoted text clipped - 16 lines] > > Katy Since dateserial number 1 (01-JAN-1900) is a Sunday, how about this?: =TEXT((SEARCH(LEFT(A1,2),"SuMoTuWeThFrSa")-1)/2+2,"dddd") Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > With A1 containing a day name (eg Tuesday) > [quoted text clipped - 32 lines] >> >> Katy HI thanks for all your replys I went with this one =TEXT((SEARCH(LEFT(C2,2),"SuMoTuWeThFrSa")-1)/2+2,"dddd") But this one worked just aswell =INDEX({"Tues","Wednes","Thurs","Fri","Satur","Sun","Mon"}, (SEARCH(LEFT(C2,2),"MoTuWeThFrSaSu")-1)/2+1)&"day" the method i used last time and was wanting to use again included something along the lines of A1+1 and i had to use the "dddd" at the end of the formula. If anyone has any idea what I am on about I would be interested to know Again I would like to thanks everyone for the replies was spending about 2 hours yesterday trying to solve this problem and i get up this morning and you guys have fixed it for me so thanks Katy If you had a real date in A1, but just formatted as dddd to show the day, you could have used =TEXT(A1+1,"dddd") Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > HI thanks for all your replys > [quoted text clipped - 16 lines] > > Katy |
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.