software

How do I write a formula for Excel to read the value in a cell that will be
Monday, Tuesday, Wednesday, etc. and respond with a corellating day.

For example, quot;Today is Monday, this report is due Thursdayquot;. I realize
Monday and Thursday will be represented in their own cells.

Thank you!

=quot;Report due on quot;amp;TEXT(A1,quot;ddddquot;)

where A1 is your date.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Red Cross Rosequot; lt;Red Cross gt; wrote in message
...
gt; How do I write a formula for Excel to read the value in a cell that will
be
gt; Monday, Tuesday, Wednesday, etc. and respond with a corellating day.
gt;
gt; For example, quot;Today is Monday, this report is due Thursdayquot;. I realize
gt; Monday and Thursday will be represented in their own cells.
gt;
gt; Thank you!
we need more info.How are the days quot;storedquot; Are they typed monday tuesday etc
or do they represent a real excel date formatted as dddd.?

--
paul
remove nospam for email addy!
quot;Red Cross Rosequot; wrote:

gt; How do I write a formula for Excel to read the value in a cell that will be
gt; Monday, Tuesday, Wednesday, etc. and respond with a corellating day.
gt;
gt; For example, quot;Today is Monday, this report is due Thursdayquot;. I realize
gt; Monday and Thursday will be represented in their own cells.
gt;
gt; Thank you!

Hi

With an Excel date in cell A1 which is a Monday e.g. 20/02/2006 then
=quot;Today is quot;amp;TEXT(A1,quot;ddddquot;)amp;quot;, this report is due quot;amp;TEXT(A1 3,quot;ddddquot;)

--
Regards

Roger Govierquot;Red Cross Rosequot; lt;Red Cross gt; wrote in
message ...
gt; How do I write a formula for Excel to read the value in a cell that
gt; will be
gt; Monday, Tuesday, Wednesday, etc. and respond with a corellating day.
gt;
gt; For example, quot;Today is Monday, this report is due Thursdayquot;. I
gt; realize
gt; Monday and Thursday will be represented in their own cells.
gt;
gt; Thank you!
The days will be manually typed in each morning. It's just a basic 2-line
entry that will say
Today is MONDAY (or whatever that day is) February 20 2006
72 hour reports for THURSDAY February 23 2006 are due today
We can manually enter the day/date on the first line, but I want excel to
automatically calculate the day/date of the reports that are due on the
second line.

Thanks Paul!

quot;paulquot; wrote:

gt; we need more info.How are the days quot;storedquot; Are they typed monday tuesday etc
gt; or do they represent a real excel date formatted as dddd.?
gt;
gt; --
gt; paul
gt; remove nospam for email addy!
gt;
gt;
gt;
gt; quot;Red Cross Rosequot; wrote:
gt;
gt; gt; How do I write a formula for Excel to read the value in a cell that will be
gt; gt; Monday, Tuesday, Wednesday, etc. and respond with a corellating day.
gt; gt;
gt; gt; For example, quot;Today is Monday, this report is due Thursdayquot;. I realize
gt; gt; Monday and Thursday will be represented in their own cells.
gt; gt;
gt; gt; Thank you!

Bob, thanks for your reply, but I'm afraid I'm too much of a novice to make
your instructions work for the calculation.

quot;Bob Phillipsquot; wrote:

gt; =quot;Report due on quot;amp;TEXT(A1,quot;ddddquot;)
gt;
gt; where A1 is your date.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Red Cross Rosequot; lt;Red Cross gt; wrote in message
gt; ...
gt; gt; How do I write a formula for Excel to read the value in a cell that will
gt; be
gt; gt; Monday, Tuesday, Wednesday, etc. and respond with a corellating day.
gt; gt;
gt; gt; For example, quot;Today is Monday, this report is due Thursdayquot;. I realize
gt; gt; Monday and Thursday will be represented in their own cells.
gt; gt;
gt; gt; Thank you!
gt;
gt;
gt;

Hi Roger, thanks for your reply!

I tried what you suggested, but I'm getting a #VALUE! error. Would you
please break this down for me? Thank you for your patience with this novice!
I obviously need a training class!

Rosequot;Roger Govierquot; wrote:

gt; Hi
gt;
gt; With an Excel date in cell A1 which is a Monday e.g. 20/02/2006 then
gt; =quot;Today is quot;amp;TEXT(A1,quot;ddddquot;)amp;quot;, this report is due quot;amp;TEXT(A1 3,quot;ddddquot;)
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;Red Cross Rosequot; lt;Red Cross gt; wrote in
gt; message ...
gt; gt; How do I write a formula for Excel to read the value in a cell that
gt; gt; will be
gt; gt; Monday, Tuesday, Wednesday, etc. and respond with a corellating day.
gt; gt;
gt; gt; For example, quot;Today is Monday, this report is due Thursdayquot;. I
gt; gt; realize
gt; gt; Monday and Thursday will be represented in their own cells.
gt; gt;
gt; gt; Thank you!
gt;
gt;
gt;

rose,cell A1 must ontain a proper excel date.In a1 type =today().Cell A1 will
now always shows todays date,and rogers formula will always show a report is
due in 3 days from today
--
paul
remove nospam for email addy!
quot;Red Cross Rosequot; wrote:

gt; Hi Roger, thanks for your reply!
gt;
gt; I tried what you suggested, but I'm getting a #VALUE! error. Would you
gt; please break this down for me? Thank you for your patience with this novice!
gt; I obviously need a training class!
gt;
gt; Rose
gt;
gt;
gt; quot;Roger Govierquot; wrote:
gt;
gt; gt; Hi
gt; gt;
gt; gt; With an Excel date in cell A1 which is a Monday e.g. 20/02/2006 then
gt; gt; =quot;Today is quot;amp;TEXT(A1,quot;ddddquot;)amp;quot;, this report is due quot;amp;TEXT(A1 3,quot;ddddquot;)
gt; gt;
gt; gt; --
gt; gt; Regards
gt; gt;
gt; gt; Roger Govier
gt; gt;
gt; gt;
gt; gt; quot;Red Cross Rosequot; lt;Red Cross gt; wrote in
gt; gt; message ...
gt; gt; gt; How do I write a formula for Excel to read the value in a cell that
gt; gt; gt; will be
gt; gt; gt; Monday, Tuesday, Wednesday, etc. and respond with a corellating day.
gt; gt; gt;
gt; gt; gt; For example, quot;Today is Monday, this report is due Thursdayquot;. I
gt; gt; gt; realize
gt; gt; gt; Monday and Thursday will be represented in their own cells.
gt; gt; gt;
gt; gt; gt; Thank you!
gt; gt;
gt; gt;
gt; gt;

Hi Rose

As Paul pointed out, you need to have a valid Excel date entered into
cell A1.
(If I type 20.02.2006 in cell A1, I also get a #VALUE error.)
I chose 20/02/2006 for next Monday's date. Do ensure it is not a Text
representation of the date.
Highlight cell A1, then Formatgt;Cellsgt;Numbergt;Date and highlight the 3rd
option down which shows 14/03/2001
Now enter 20/02/2006 in the cell (or, whatever the format is for your
regional settings)

To provide a result exactly the same as your second post to Paul, then
use the following formula

=quot;Today is quot;amp;TEXT(A1,quot;ddddquot;)amp;quot; quot;amp;TEXT(A1,quot;mmmm dd yyyyquot;)
amp; CHAR(10)amp; quot;72 hour reports for quot; amp;UPPER(TEXT(A1 3,quot;ddddquot;))
amp;quot; quot;amp;TEXT(A1 3,quot;mmmm dd yyyyquot;)amp;quot; are due todayquot;

I have deliberately forced line breaks into the formula to allow it to
come out more clearly in this posting. The formula is really just one
continuous entry.

If you format the cell in which you post the formula with line wrap on,
then it will show on 2 lines as you requested, once you widen the
column.
Formatgt;Cellsgt;Alignmentgt;Wrap text

--
Regards

Roger Govierquot;Red Cross Rosequot; gt; wrote in
message ...
gt; Hi Roger, thanks for your reply!
gt;
gt; I tried what you suggested, but I'm getting a #VALUE! error. Would
gt; you
gt; please break this down for me? Thank you for your patience with this
gt; novice!
gt; I obviously need a training class!
gt;
gt; Rose
gt;
gt;
gt; quot;Roger Govierquot; wrote:
gt;
gt;gt; Hi
gt;gt;
gt;gt; With an Excel date in cell A1 which is a Monday e.g. 20/02/2006 then
gt;gt; =quot;Today is quot;amp;TEXT(A1,quot;ddddquot;)amp;quot;, this report is due
gt;gt; quot;amp;TEXT(A1 3,quot;ddddquot;)
gt;gt;
gt;gt; --
gt;gt; Regards
gt;gt;
gt;gt; Roger Govier
gt;gt;
gt;gt;
gt;gt; quot;Red Cross Rosequot; lt;Red Cross gt; wrote in
gt;gt; message ...
gt;gt; gt; How do I write a formula for Excel to read the value in a cell that
gt;gt; gt; will be
gt;gt; gt; Monday, Tuesday, Wednesday, etc. and respond with a corellating
gt;gt; gt; day.
gt;gt; gt;
gt;gt; gt; For example, quot;Today is Monday, this report is due Thursdayquot;. I
gt;gt; gt; realize
gt;gt; gt; Monday and Thursday will be represented in their own cells.
gt;gt; gt;
gt;gt; gt; Thank you!
gt;gt;
gt;gt;
gt;gt;
If you are going to type the date each morning then why not make it
completetly automatic.......in the first line (cell) type =TODAY() and format
the cell as dddd to show the day, in the second line (cell) type =quot;72 hour
reports for quot;amp;TEXT(TODAY() 3,quot;ddd mmm dd, yyyyquot;)amp;quot; is due todayquot;

But what about the weekend? Does saturday and sunday count in the 72 hours?

HTH
Jean-Guy

quot;Red Cross Rosequot; wrote:

gt; The days will be manually typed in each morning. It's just a basic 2-line
gt; entry that will say
gt; Today is MONDAY (or whatever that day is) February 20 2006
gt; 72 hour reports for THURSDAY February 23 2006 are due today
gt; We can manually enter the day/date on the first line, but I want excel to
gt; automatically calculate the day/date of the reports that are due on the
gt; second line.
gt;
gt; Thanks Paul!
gt;
gt; quot;paulquot; wrote:
gt;
gt; gt; we need more info.How are the days quot;storedquot; Are they typed monday tuesday etc
gt; gt; or do they represent a real excel date formatted as dddd.?
gt; gt;
gt; gt; --
gt; gt; paul
gt; gt; remove nospam for email addy!
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Red Cross Rosequot; wrote:
gt; gt;
gt; gt; gt; How do I write a formula for Excel to read the value in a cell that will be
gt; gt; gt; Monday, Tuesday, Wednesday, etc. and respond with a corellating day.
gt; gt; gt;
gt; gt; gt; For example, quot;Today is Monday, this report is due Thursdayquot;. I realize
gt; gt; gt; Monday and Thursday will be represented in their own cells.
gt; gt; gt;
gt; gt; gt; Thank you!