7 days of the week

I have a 7-column single row worksheet.

Cell A1 contains the forumla "=TODAY()".

I have two issues:

(a) I want to format A1 as simply the first letter of the name of the day, e.g.:
sunday is S
saturday is S
monday is M, and so on

...currently I have it formatted Custom, type "ddd" and it gives me Sun, Sat, Mon, etc., not sure what to tweak to get what I want.

(b) I want the remaining 6 columns to be oututs, in order, based on the day that fills A1, e.g.:
if A1 = a sunday, then A1:G1 should read, S | M | T | W | T | F | S
if A1 = a monday, then A1:G1 should read, M | T | W | T | F | S | S

Any help with these would be appreciated.

- Jim

One way:

In A1 type: =CHOOSE(WEEKDAY(TODAY()),"S","M","T","W","T","F","S")

In B1 type: =CHOOSE(IF(WEEKDAY(TODAY()+1)=8,1,WEEKDAY(TODAY()+1)),"S","M","T","W","T","F","S")

In C1 type: =CHOOSE(IF(WEEKDAY(TODAY()+2)=8,1,WEEKDAY(TODAY()+2)),"S","M","T","W","T","F","S")

etc. (changing only the number added to TODAY() in both instances in each formula

that did it
tx cutter!

You're welcome. Thanks for acknowledging.

On Sat, 8 Oct 2005 21:29:55 -0500, James C
<James.C.1wm6ya_1128827103.6859@excelforum-nospam.com> wrote:

>
>I have a 7-column single row worksheet.
>
>Cell A1 contains the forumla "=TODAY()".
>
>I have two issues:
>
>(a) I want to format A1 as simply the first letter of the name of the
>day, e.g.:
>sunday is S
>saturday is S
>monday is M, and so on
>
>..currently I have it formatted Custom, type "ddd" and it gives me
>Sun, Sat, Mon, etc., not sure what to tweak to get what I want.
>
>(b) I want the remaining 6 columns to be oututs, in order, based on the
>day that fills A1, e.g.:
>if A1 = a sunday, then A1:G1 should read, S | M | T | W | T | F | S
>if A1 = a monday, then A1:G1 should read, M | T | W | T | F | S | S
>
>Any help with these would be appreciated.
>
>- Jim

You cannot do what you want to do with formatting.

Here's one solution:

A1: =LEFT(TEXT(TODAY()+COLUMN()-1,"ddd"),1)

Then copy/drag across to G1


--ron

This is working for me:
=VLOOKUP(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()+COLUMN(A1)-1))),{1,"S";2,"M";3,"T";4,"W";5,"T";6,"F";7,"S"},2,0)

Format the cells as General.
Make sure that the formula is all on one line, if you paste it from this
post into your spreadsheet.

--
tj


"James C" wrote:

>
> I have a 7-column single row worksheet.
>
> Cell A1 contains the forumla "=TODAY()".
>
> I have two issues:
>
> (a) I want to format A1 as simply the first letter of the name of the
> day, e.g.:
> sunday is S
> saturday is S
> monday is M, and so on
>
> ...currently I have it formatted Custom, type "ddd" and it gives me
> Sun, Sat, Mon, etc., not sure what to tweak to get what I want.
>
> (b) I want the remaining 6 columns to be oututs, in order, based on the
> day that fills A1, e.g.:
> if A1 = a sunday, then A1:G1 should read, S | M | T | W | T | F | S
> if A1 = a monday, then A1:G1 should read, M | T | W | T | F | S | S
>
> Any help with these would be appreciated.
>
> - Jim
>
>
> --
> James C
> ------------------------------------------------------------------------
> James C's Profile: http://www.excelforum.com/member.php...o&userid=12114
> View this thread: http://www.excelforum.com/showthread...hreadid=474538
>
>

> Here's one solution:
>
> A1: =LEFT(TEXT(TODAY()+COLUMN()-1,"ddd"),1)

I like that one, Ron: nice and simple.

--
tj


"Ron Rosenfeld" wrote:

> On Sat, 8 Oct 2005 21:29:55 -0500, James C
> <James.C.1wm6ya_1128827103.6859@excelforum-nospam.com> wrote:
>
> >
> >I have a 7-column single row worksheet.
> >
> >Cell A1 contains the forumla "=TODAY()".
> >
> >I have two issues:
> >
> >(a) I want to format A1 as simply the first letter of the name of the
> >day, e.g.:
> >sunday is S
> >saturday is S
> >monday is M, and so on
> >
> >..currently I have it formatted Custom, type "ddd" and it gives me
> >Sun, Sat, Mon, etc., not sure what to tweak to get what I want.
> >
> >(b) I want the remaining 6 columns to be oututs, in order, based on the
> >day that fills A1, e.g.:
> >if A1 = a sunday, then A1:G1 should read, S | M | T | W | T | F | S
> >if A1 = a monday, then A1:G1 should read, M | T | W | T | F | S | S
> >
> >Any help with these would be appreciated.
> >
> >- Jim
>
> You cannot do what you want to do with formatting.
>
> Here's one solution:
>
> A1: =LEFT(TEXT(TODAY()+COLUMN()-1,"ddd"),1)
>
> Then copy/drag across to G1
>
>
> --ron
>

On Sat, 8 Oct 2005 21:14:01 -0700, "tjtjjtjt"
<tjtjjtjt@discussions.microsoft.com> wrote:

>> Here's one solution:
>>
>> A1: =LEFT(TEXT(TODAY()+COLUMN()-1,"ddd"),1)
>
>I like that one, Ron: nice and simple.
>
>--
>tj
>

Thank you. Although I believe the OP did not see it.


--ron

You can avoid the VLOOKUP formula with the MID function:

=MID("SMTWTFS",WEEKDAY(TODAY()+COLUMN()-1),1)


On Sat, 8 Oct 2005 21:10:04 -0700, "tjtjjtjt"
<tjtjjtjt@discussions.microsoft.com> wrote:

>This is working for me:
>=VLOOKUP(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()+COLUMN(A1)-1))),{1,"S";2,"M";3,"T";4,"W";5,"T";6,"F";7,"S"},2,0)
>
>Format the cells as General.
>Make sure that the formula is all on one line, if you paste it from this
>post into your spreadsheet.