I figured out how to add a calendar control to my excel spreadsheet.
For example, when I click in cell B7, the calendar appears, I click on a
November 11, the date of 11/11/08 gets entered into cell B7, and the calendar
disappears. This works Fine. However, what I would like to be able to set
up
is when the person clicks on November 11, the date of November gets entered
into cell B7 AND the corresponding weekday (in this case, Tuesday) gets
automatically entered into the cell next to it, C7.
i.e. cell B7 returns the DATE
cell C7 retunrs the DAY of week
The calendar control is in column B - Range B7:B56

G'day

This is not too hard to do once you know. The WEEKDAY function will give
you a number that returns the day of the week e.g. "=WEEKDAY(B7)"

This actually gives you an integer in the range 1 to 7 (1 for Sunday, 5 for
Thursday etc etc). You can then use a VLOOKUP to link the integers to the
names of the days via a little table that you create in another part of the
sheet or file.

If you find that your Excel environment doesn't recognise WEEKDAY, you will
need to go to Tools --- AddIns and turn on the one called "Analysis Tool
Pack" (then close the file and reopen it).
--
Time is just the thing that keeps everything from happening all at once


"retired bill" wrote:

> I figured out how to add a calendar control to my excel spreadsheet.
> For example, when I click in cell B7, the calendar appears, I click on a
> November 11, the date of 11/11/08 gets entered into cell B7, and the calendar
> disappears. This works Fine. However, what I would like to be able to set
> up
> is when the person clicks on November 11, the date of November gets entered
> into cell B7 AND the corresponding weekday (in this case, Tuesday) gets
> automatically entered into the cell next to it, C7.
> i.e. cell B7 returns the DATE
> cell C7 retunrs the DAY of week
> The calendar control is in column B - Range B7:B56
>
>
>

You can use the TEXT function to return the day name directly (and without
the need for the Analysis ToolPak)...

=TEXT(B7,"dddd")

--
Rick (MVP - Excel)


"Neptune Dinosaur" <(E-Mail Removed)> wrote in message
news:1AE2F76B-006F-4A21-AC3B-(E-Mail Removed)...
> G'day
>
> This is not too hard to do once you know. The WEEKDAY function will give
> you a number that returns the day of the week e.g. "=WEEKDAY(B7)"
>
> This actually gives you an integer in the range 1 to 7 (1 for Sunday, 5
> for
> Thursday etc etc). You can then use a VLOOKUP to link the integers to the
> names of the days via a little table that you create in another part of
> the
> sheet or file.
>
> If you find that your Excel environment doesn't recognise WEEKDAY, you
> will
> need to go to Tools --- AddIns and turn on the one called "Analysis Tool
> Pack" (then close the file and reopen it).
> --
> Time is just the thing that keeps everything from happening all at once
>
>
> "retired bill" wrote:
>
>> I figured out how to add a calendar control to my excel spreadsheet.
>> For example, when I click in cell B7, the calendar appears, I click on a
>> November 11, the date of 11/11/08 gets entered into cell B7, and the
>> calendar
>> disappears. This works Fine. However, what I would like to be able to
>> set
>> up
>> is when the person clicks on November 11, the date of November gets
>> entered
>> into cell B7 AND the corresponding weekday (in this case, Tuesday) gets
>> automatically entered into the cell next to it, C7.
>> i.e. cell B7 returns the DATE
>> cell C7 retunrs the DAY of week
>> The calendar control is in column B - Range B7:B56
>>
>>
>>

Gee, that's neat. Wish I'd discovered that 10 years ago. Every time you
start to think you've become an "Advnced User", something like this comes
along to cut you down to size ....
--
Time is just the thing that keeps everything from happening all at once


"Rick Rothstein" wrote:

> You can use the TEXT function to return the day name directly (and without
> the need for the Analysis ToolPak)...
>
> =TEXT(B7,"dddd")
>
> --
> Rick (MVP - Excel)
>
>
> "Neptune Dinosaur" <(E-Mail Removed)> wrote in message
> news:1AE2F76B-006F-4A21-AC3B-(E-Mail Removed)...
> > G'day
> >
> > This is not too hard to do once you know. The WEEKDAY function will give
> > you a number that returns the day of the week e.g. "=WEEKDAY(B7)"
> >
> > This actually gives you an integer in the range 1 to 7 (1 for Sunday, 5
> > for
> > Thursday etc etc). You can then use a VLOOKUP to link the integers to the
> > names of the days via a little table that you create in another part of
> > the
> > sheet or file.
> >
> > If you find that your Excel environment doesn't recognise WEEKDAY, you
> > will
> > need to go to Tools --- AddIns and turn on the one called "Analysis Tool
> > Pack" (then close the file and reopen it).
> > --
> > Time is just the thing that keeps everything from happening all at once
> >
> >
> > "retired bill" wrote:
> >
> >> I figured out how to add a calendar control to my excel spreadsheet.
> >> For example, when I click in cell B7, the calendar appears, I click on a
> >> November 11, the date of 11/11/08 gets entered into cell B7, and the
> >> calendar
> >> disappears. This works Fine. However, what I would like to be able to
> >> set
> >> up
> >> is when the person clicks on November 11, the date of November gets
> >> entered
> >> into cell B7 AND the corresponding weekday (in this case, Tuesday) gets
> >> automatically entered into the cell next to it, C7.
> >> i.e. cell B7 returns the DATE
> >> cell C7 retunrs the DAY of week
> >> The calendar control is in column B - Range B7:B56
> >>
> >>
> >>
>
>

If you use 3 d's, then the abbreviated day name is returned. You can do the
same thing (using 3 and 4 m's) to return the month name (abbreviated and
full). It looks like 5 m's will return the first letter of the month name (5
d's doesn't do this).

--
Rick (MVP - Excel)


"Neptune Dinosaur" <(E-Mail Removed)> wrote in message
news:01017525-9BBE-4E11-B723-(E-Mail Removed)...
> Gee, that's neat. Wish I'd discovered that 10 years ago. Every time you
> start to think you've become an "Advnced User", something like this comes
> along to cut you down to size ....
> --
> Time is just the thing that keeps everything from happening all at once
>
>
> "Rick Rothstein" wrote:
>
>> You can use the TEXT function to return the day name directly (and
>> without
>> the need for the Analysis ToolPak)...
>>
>> =TEXT(B7,"dddd")
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Neptune Dinosaur" <(E-Mail Removed)> wrote in message
>> news:1AE2F76B-006F-4A21-AC3B-(E-Mail Removed)...
>> > G'day
>> >
>> > This is not too hard to do once you know. The WEEKDAY function will
>> > give
>> > you a number that returns the day of the week e.g. "=WEEKDAY(B7)"
>> >
>> > This actually gives you an integer in the range 1 to 7 (1 for Sunday, 5
>> > for
>> > Thursday etc etc). You can then use a VLOOKUP to link the integers to
>> > the
>> > names of the days via a little table that you create in another part of
>> > the
>> > sheet or file.
>> >
>> > If you find that your Excel environment doesn't recognise WEEKDAY, you
>> > will
>> > need to go to Tools --- AddIns and turn on the one called "Analysis
>> > Tool
>> > Pack" (then close the file and reopen it).
>> > --
>> > Time is just the thing that keeps everything from happening all at once
>> >
>> >
>> > "retired bill" wrote:
>> >
>> >> I figured out how to add a calendar control to my excel spreadsheet.
>> >> For example, when I click in cell B7, the calendar appears, I click on
>> >> a
>> >> November 11, the date of 11/11/08 gets entered into cell B7, and the
>> >> calendar
>> >> disappears. This works Fine. However, what I would like to be able
>> >> to
>> >> set
>> >> up
>> >> is when the person clicks on November 11, the date of November gets
>> >> entered
>> >> into cell B7 AND the corresponding weekday (in this case, Tuesday)
>> >> gets
>> >> automatically entered into the cell next to it, C7.
>> >> i.e. cell B7 returns the DATE
>> >> cell C7 retunrs the DAY of week
>> >> The calendar control is in column B - Range B7:B56
>> >>
>> >>
>> >>
>>
>>

Yep, I'd already gone in and discovered the "3d" thing and the month
equivalent. I have a big application that's used by about 50 people her , so
I've already begun to simplify all of the relevant formaulae and structures.

Thanks again.
--
Time is just the thing that keeps everything from happening all at once


"Rick Rothstein" wrote:

> If you use 3 d's, then the abbreviated day name is returned. You can do the
> same thing (using 3 and 4 m's) to return the month name (abbreviated and
> full). It looks like 5 m's will return the first letter of the month name (5
> d's doesn't do this).
>
> --
> Rick (MVP - Excel)
>
>
> "Neptune Dinosaur" <(E-Mail Removed)> wrote in message
> news:01017525-9BBE-4E11-B723-(E-Mail Removed)...
> > Gee, that's neat. Wish I'd discovered that 10 years ago. Every time you
> > start to think you've become an "Advnced User", something like this comes
> > along to cut you down to size ....
> > --
> > Time is just the thing that keeps everything from happening all at once
> >
> >
> > "Rick Rothstein" wrote:
> >
> >> You can use the TEXT function to return the day name directly (and
> >> without
> >> the need for the Analysis ToolPak)...
> >>
> >> =TEXT(B7,"dddd")
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >> "Neptune Dinosaur" <(E-Mail Removed)> wrote in message
> >> news:1AE2F76B-006F-4A21-AC3B-(E-Mail Removed)...
> >> > G'day
> >> >
> >> > This is not too hard to do once you know. The WEEKDAY function will
> >> > give
> >> > you a number that returns the day of the week e.g. "=WEEKDAY(B7)"
> >> >
> >> > This actually gives you an integer in the range 1 to 7 (1 for Sunday, 5
> >> > for
> >> > Thursday etc etc). You can then use a VLOOKUP to link the integers to
> >> > the
> >> > names of the days via a little table that you create in another part of
> >> > the
> >> > sheet or file.
> >> >
> >> > If you find that your Excel environment doesn't recognise WEEKDAY, you
> >> > will
> >> > need to go to Tools --- AddIns and turn on the one called "Analysis
> >> > Tool
> >> > Pack" (then close the file and reopen it).
> >> > --
> >> > Time is just the thing that keeps everything from happening all at once
> >> >
> >> >
> >> > "retired bill" wrote:
> >> >
> >> >> I figured out how to add a calendar control to my excel spreadsheet.
> >> >> For example, when I click in cell B7, the calendar appears, I click on
> >> >> a
> >> >> November 11, the date of 11/11/08 gets entered into cell B7, and the
> >> >> calendar
> >> >> disappears. This works Fine. However, what I would like to be able
> >> >> to
> >> >> set
> >> >> up
> >> >> is when the person clicks on November 11, the date of November gets
> >> >> entered
> >> >> into cell B7 AND the corresponding weekday (in this case, Tuesday)
> >> >> gets
> >> >> automatically entered into the cell next to it, C7.
> >> >> i.e. cell B7 returns the DATE
> >> >> cell C7 retunrs the DAY of week
> >> >> The calendar control is in column B - Range B7:B56
> >> >>
> >> >>
> >> >>
> >>
> >>
>
>

Rick, thanks for your answer. This is a very simple answer to this question,
however, when I insert the formula, then copy and paste for the range in the
column, it displays the SAME day of the week for the entire column, until I
use the calendar control and actually click on a date I have picked. I would
like to have the cell return a BLANK, until I use the calendar control and
click on a date, then enter the correct DAY of the week in the cell.

Thanks for your time, Bill

"Rick Rothstein" wrote:

> You can use the TEXT function to return the day name directly (and without
> the need for the Analysis ToolPak)...
>
> =TEXT(B7,"dddd")
>
> --
> Rick (MVP - Excel)
>
>
> "Neptune Dinosaur" <(E-Mail Removed)> wrote in message
> news:1AE2F76B-006F-4A21-AC3B-(E-Mail Removed)...
> > G'day
> >
> > This is not too hard to do once you know. The WEEKDAY function will give
> > you a number that returns the day of the week e.g. "=WEEKDAY(B7)"
> >
> > This actually gives you an integer in the range 1 to 7 (1 for Sunday, 5
> > for
> > Thursday etc etc). You can then use a VLOOKUP to link the integers to the
> > names of the days via a little table that you create in another part of
> > the
> > sheet or file.
> >
> > If you find that your Excel environment doesn't recognise WEEKDAY, you
> > will
> > need to go to Tools --- AddIns and turn on the one called "Analysis Tool
> > Pack" (then close the file and reopen it).
> > --
> > Time is just the thing that keeps everything from happening all at once
> >
> >
> > "retired bill" wrote:
> >
> >> I figured out how to add a calendar control to my excel spreadsheet.
> >> For example, when I click in cell B7, the calendar appears, I click on a
> >> November 11, the date of 11/11/08 gets entered into cell B7, and the
> >> calendar
> >> disappears. This works Fine. However, what I would like to be able to
> >> set
> >> up
> >> is when the person clicks on November 11, the date of November gets
> >> entered
> >> into cell B7 AND the corresponding weekday (in this case, Tuesday) gets
> >> automatically entered into the cell next to it, C7.
> >> i.e. cell B7 returns the DATE
> >> cell C7 retunrs the DAY of week
> >> The calendar control is in column B - Range B7:B56
> >>
> >>
> >>
>
>

=IF(B7="","",TEXT(B7,"dddd"))

Copy down.


Gord Dibben MS Excel MVP


On Thu, 7 May 2009 09:16:06 -0700, retired bill
<(E-Mail Removed)> wrote:

>Rick, thanks for your answer. This is a very simple answer to this question,
>however, when I insert the formula, then copy and paste for the range in the
>column, it displays the SAME day of the week for the entire column, until I
>use the calendar control and actually click on a date I have picked. I would
>like to have the cell return a BLANK, until I use the calendar control and
>click on a date, then enter the correct DAY of the week in the cell.
>
>Thanks for your time, Bill
>
>"Rick Rothstein" wrote:
>
>> You can use the TEXT function to return the day name directly (and without
>> the need for the Analysis ToolPak)...
>>
>> =TEXT(B7,"dddd")
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Neptune Dinosaur" <(E-Mail Removed)> wrote in message
>> news:1AE2F76B-006F-4A21-AC3B-(E-Mail Removed)...
>> > G'day
>> >
>> > This is not too hard to do once you know. The WEEKDAY function will give
>> > you a number that returns the day of the week e.g. "=WEEKDAY(B7)"
>> >
>> > This actually gives you an integer in the range 1 to 7 (1 for Sunday, 5
>> > for
>> > Thursday etc etc). You can then use a VLOOKUP to link the integers to the
>> > names of the days via a little table that you create in another part of
>> > the
>> > sheet or file.
>> >
>> > If you find that your Excel environment doesn't recognise WEEKDAY, you
>> > will
>> > need to go to Tools --- AddIns and turn on the one called "Analysis Tool
>> > Pack" (then close the file and reopen it).
>> > --
>> > Time is just the thing that keeps everything from happening all at once
>> >
>> >
>> > "retired bill" wrote:
>> >
>> >> I figured out how to add a calendar control to my excel spreadsheet.
>> >> For example, when I click in cell B7, the calendar appears, I click on a
>> >> November 11, the date of 11/11/08 gets entered into cell B7, and the
>> >> calendar
>> >> disappears. This works Fine. However, what I would like to be able to
>> >> set
>> >> up
>> >> is when the person clicks on November 11, the date of November gets
>> >> entered
>> >> into cell B7 AND the corresponding weekday (in this case, Tuesday) gets
>> >> automatically entered into the cell next to it, C7.
>> >> i.e. cell B7 returns the DATE
>> >> cell C7 retunrs the DAY of week
>> >> The calendar control is in column B - Range B7:B56
>> >>
>> >>
>> >>
>>
>>

Gord, thanks for your reply, will give it a try. The reason I posted on a
new thread is because I am new to this, and was not sure anyone could OR
would go back the original thread to check a later response I posted. thanks

New Question: I am using the "ddd" setup in the formula and would like for
the three letter day of the week to appear in ALL capitals if possible. I
have tried changing the "ddd" to "DDD", but this did not work. Any help here
would be greatly appreciated. Thanks, Bill

"Gord Dibben" wrote:

> =IF(B7="","",TEXT(B7,"dddd"))
>
> Copy down.
>
>
> Gord Dibben MS Excel MVP
>
>
> On Thu, 7 May 2009 09:16:06 -0700, retired bill
> <(E-Mail Removed)> wrote:
>
> >Rick, thanks for your answer. This is a very simple answer to this question,
> >however, when I insert the formula, then copy and paste for the range in the
> >column, it displays the SAME day of the week for the entire column, until I
> >use the calendar control and actually click on a date I have picked. I would
> >like to have the cell return a BLANK, until I use the calendar control and
> >click on a date, then enter the correct DAY of the week in the cell.
> >
> >Thanks for your time, Bill
> >
> >"Rick Rothstein" wrote:
> >
> >> You can use the TEXT function to return the day name directly (and without
> >> the need for the Analysis ToolPak)...
> >>
> >> =TEXT(B7,"dddd")
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >> "Neptune Dinosaur" <(E-Mail Removed)> wrote in message
> >> news:1AE2F76B-006F-4A21-AC3B-(E-Mail Removed)...
> >> > G'day
> >> >
> >> > This is not too hard to do once you know. The WEEKDAY function will give
> >> > you a number that returns the day of the week e.g. "=WEEKDAY(B7)"
> >> >
> >> > This actually gives you an integer in the range 1 to 7 (1 for Sunday, 5
> >> > for
> >> > Thursday etc etc). You can then use a VLOOKUP to link the integers to the
> >> > names of the days via a little table that you create in another part of
> >> > the
> >> > sheet or file.
> >> >
> >> > If you find that your Excel environment doesn't recognise WEEKDAY, you
> >> > will
> >> > need to go to Tools --- AddIns and turn on the one called "Analysis Tool
> >> > Pack" (then close the file and reopen it).
> >> > --
> >> > Time is just the thing that keeps everything from happening all at once
> >> >
> >> >
> >> > "retired bill" wrote:
> >> >
> >> >> I figured out how to add a calendar control to my excel spreadsheet.
> >> >> For example, when I click in cell B7, the calendar appears, I click on a
> >> >> November 11, the date of 11/11/08 gets entered into cell B7, and the
> >> >> calendar
> >> >> disappears. This works Fine. However, what I would like to be able to
> >> >> set
> >> >> up
> >> >> is when the person clicks on November 11, the date of November gets
> >> >> entered
> >> >> into cell B7 AND the corresponding weekday (in this case, Tuesday) gets
> >> >> automatically entered into the cell next to it, C7.
> >> >> i.e. cell B7 returns the DATE
> >> >> cell C7 retunrs the DAY of week
> >> >> The calendar control is in column B - Range B7:B56
> >> >>
> >> >>
> >> >>
> >>
> >>
>
>

Use the UPPER function...

=IF(B7="","",UPPER(TEXT(B7,"ddd")))

--
Rick (MVP - Excel)


"retired bill" <(E-Mail Removed)> wrote in message
news2A8C393-94C6-406C-A006-(E-Mail Removed)...
> Gord, thanks for your reply, will give it a try. The reason I posted on a
> new thread is because I am new to this, and was not sure anyone could OR
> would go back the original thread to check a later response I posted.
> thanks
>
> New Question: I am using the "ddd" setup in the formula and would like
> for
> the three letter day of the week to appear in ALL capitals if possible. I
> have tried changing the "ddd" to "DDD", but this did not work. Any help
> here
> would be greatly appreciated. Thanks, Bill
>
> "Gord Dibben" wrote:
>
>> =IF(B7="","",TEXT(B7,"dddd"))
>>
>> Copy down.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>> On Thu, 7 May 2009 09:16:06 -0700, retired bill
>> <(E-Mail Removed)> wrote:
>>
>> >Rick, thanks for your answer. This is a very simple answer to this
>> >question,
>> >however, when I insert the formula, then copy and paste for the range in
>> >the
>> >column, it displays the SAME day of the week for the entire column,
>> >until I
>> >use the calendar control and actually click on a date I have picked. I
>> >would
>> >like to have the cell return a BLANK, until I use the calendar control
>> >and
>> >click on a date, then enter the correct DAY of the week in the cell.
>> >
>> >Thanks for your time, Bill
>> >
>> >"Rick Rothstein" wrote:
>> >
>> >> You can use the TEXT function to return the day name directly (and
>> >> without
>> >> the need for the Analysis ToolPak)...
>> >>
>> >> =TEXT(B7,"dddd")
>> >>
>> >> --
>> >> Rick (MVP - Excel)
>> >>
>> >>
>> >> "Neptune Dinosaur" <(E-Mail Removed)> wrote in message
>> >> news:1AE2F76B-006F-4A21-AC3B-(E-Mail Removed)...
>> >> > G'day
>> >> >
>> >> > This is not too hard to do once you know. The WEEKDAY function will
>> >> > give
>> >> > you a number that returns the day of the week e.g. "=WEEKDAY(B7)"
>> >> >
>> >> > This actually gives you an integer in the range 1 to 7 (1 for
>> >> > Sunday, 5
>> >> > for
>> >> > Thursday etc etc). You can then use a VLOOKUP to link the integers
>> >> > to the
>> >> > names of the days via a little table that you create in another part
>> >> > of
>> >> > the
>> >> > sheet or file.
>> >> >
>> >> > If you find that your Excel environment doesn't recognise WEEKDAY,
>> >> > you
>> >> > will
>> >> > need to go to Tools --- AddIns and turn on the one called "Analysis
>> >> > Tool
>> >> > Pack" (then close the file and reopen it).
>> >> > --
>> >> > Time is just the thing that keeps everything from happening all at
>> >> > once
>> >> >
>> >> >
>> >> > "retired bill" wrote:
>> >> >
>> >> >> I figured out how to add a calendar control to my excel
>> >> >> spreadsheet.
>> >> >> For example, when I click in cell B7, the calendar appears, I click
>> >> >> on a
>> >> >> November 11, the date of 11/11/08 gets entered into cell B7, and
>> >> >> the
>> >> >> calendar
>> >> >> disappears. This works Fine. However, what I would like to be
>> >> >> able to
>> >> >> set
>> >> >> up
>> >> >> is when the person clicks on November 11, the date of November gets
>> >> >> entered
>> >> >> into cell B7 AND the corresponding weekday (in this case, Tuesday)
>> >> >> gets
>> >> >> automatically entered into the cell next to it, C7.
>> >> >> i.e. cell B7 returns the DATE
>> >> >> cell C7 retunrs the DAY of week
>> >> >> The calendar control is in column B - Range B7:B56
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>