Adding Days that are in the form of Text eg monday, tuesday

  • Thread starter Thread starter Katy
  • Start date Start date
K

Katy

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

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
=LOOKUP(A1,
{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday",
"Sa­turday"},
{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday",
"Su­nday"})
Click to expand...

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.
Click to expand...

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
Click to expand...

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
=LOOKUP(A1,
{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday",
"Sa­turday"},
{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday",
"Su­nday"})
Click to expand...

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"})
 
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)
 
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)
 
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
 
Yes of course. I only tested on a few days and it worked. Foolish me!

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



=LOOKUP(A1,
{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday",
"Sa­turday"},
{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday",
"Su­nday"})
Click to expand...

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

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top