[Walter Briscoe]

Strictly speaking, this is not topical.

I run Word & Excel 2003 and have an Excel data source, giving me grief.

I have a text field (B2) containing a date. e.g. for today, it has 0516
meaning Sunday, May 16, 2010.
Another date field (C2), set to =TEXT(B2,"dddd"), has value Thursday and
I want the value Sunday.
When B2 is 1, =TEXT(B2,"mmm-dd-yyyy") has value Jan-01-1900. i.e. My
number is taken as the date offset by Jan-01-1900.

What hopefully simple formula should I put in C2 to calculate Sunday
from 0516 in B2. =TEXT(B2 + 1st January this year, "dddd") is tortuous.

I have freedom to change anything, but am attached to my "mmdd" date.
I would really prefer not to change my Word document.

Thanks!
--
Walter Briscoe

[Graham Mayor]

0516 is not a date and you cannot calculate the day of the week from it as
it doesn't contain any reference to the year. You may know that the year is
2010, but the PC cannot read your mind.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"Walter Briscoe" wrote in message
...
Strictly speaking, this is not topical.

I run Word & Excel 2003 and have an Excel data source, giving me grief.

I have a text field (B2) containing a date. e.g. for today, it has 0516
meaning Sunday, May 16, 2010.
Another date field (C2), set to =TEXT(B2,"dddd"), has value Thursday and
I want the value Sunday.
When B2 is 1, =TEXT(B2,"mmm-dd-yyyy") has value Jan-01-1900. i.e. My
number is taken as the date offset by Jan-01-1900.

What hopefully simple formula should I put in C2 to calculate Sunday
from 0516 in B2. =TEXT(B2 + 1st January this year, "dddd") is tortuous.

I have freedom to change anything, but am attached to my "mmdd" date.
I would really prefer not to change my Word document.

Thanks!
--
Walter Briscoe

[Graham Mayor]

0516 is not a date and you cannot calculate the day of the week from it as
it doesn't contain any reference to the year. You may know that the year is
2010, but the PC cannot read your mind.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"Walter Briscoe" wrote in message
...
Strictly speaking, this is not topical.

I run Word & Excel 2003 and have an Excel data source, giving me grief.

I have a text field (B2) containing a date. e.g. for today, it has 0516
meaning Sunday, May 16, 2010.
Another date field (C2), set to =TEXT(B2,"dddd"), has value Thursday and
I want the value Sunday.
When B2 is 1, =TEXT(B2,"mmm-dd-yyyy") has value Jan-01-1900. i.e. My
number is taken as the date offset by Jan-01-1900.

What hopefully simple formula should I put in C2 to calculate Sunday
from 0516 in B2. =TEXT(B2 + 1st January this year, "dddd") is tortuous.

I have freedom to change anything, but am attached to my "mmdd" date.
I would really prefer not to change my Word document.

Thanks!
--
Walter Briscoe

[Peter Jamieson]

How about

=TEXT(DATEVALUE(LEFT(B1,2) & "/" & RIGHT(B1,2)),"mmm-dd-yyyy")

or

=TEXT(DATEVALUE(RIGHT(B1,2) & "/" & LEFT(B1,2)),"mmm-dd-yyyy")

(depending on your regional settings)?

Peter Jamieson

http://tips.pjmsn.me.uk

On 16/05/2010 08:29, Walter Briscoe wrote:
Strictly speaking, this is not topical.

I run Word& Excel 2003 and have an Excel data source, giving me grief.

I have a text field (B2) containing a date. e.g. for today, it has 0516
meaning Sunday, May 16, 2010.
Another date field (C2), set to =TEXT(B2,"dddd"), has value Thursday and
I want the value Sunday.
When B2 is 1, =TEXT(B2,"mmm-dd-yyyy") has value Jan-01-1900. i.e. My
number is taken as the date offset by Jan-01-1900.

What hopefully simple formula should I put in C2 to calculate Sunday
from 0516 in B2. =TEXT(B2 + 1st January this year, "dddd") is tortuous.

I have freedom to change anything, but am attached to my "mmdd" date.
I would really prefer not to change my Word document.

Thanks!

[macropod[_2_]]

Hi Walter,

You can convert your field value to a day name via the mailmerge process, using a field in Word coded as:
{QUOTE{={Mergefield MyDate}*100 \# "00'-'00'-'{DATE \@ "YYYY"}"} \@ "DDDD"}
and the full date using field coding like:
{QUOTE{={Mergefield MyDate}*100 \# "00'-'00'-'{DATE \@ "YYYY"}"} \@ "DDDD, D MMMM YYYY"}

No change to your source data are required and the change to your Word mailmerge main document concerns only the field coding and
has no effect on how the text in the output documents is expressed.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[Microsoft MVP - Word]


"Walter Briscoe" wrote in message ...
Strictly speaking, this is not topical.

I run Word & Excel 2003 and have an Excel data source, giving me grief.

I have a text field (B2) containing a date. e.g. for today, it has 0516
meaning Sunday, May 16, 2010.
Another date field (C2), set to =TEXT(B2,"dddd"), has value Thursday and
I want the value Sunday.
When B2 is 1, =TEXT(B2,"mmm-dd-yyyy") has value Jan-01-1900. i.e. My
number is taken as the date offset by Jan-01-1900.

What hopefully simple formula should I put in C2 to calculate Sunday
from 0516 in B2. =TEXT(B2 + 1st January this year, "dddd") is tortuous.

I have freedom to change anything, but am attached to my "mmdd" date.
I would really prefer not to change my Word document.

Thanks!
--
Walter Briscoe