Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Walter Briscoe Walter Briscoe is offline
external usenet poster
 
Posts: 42
Default Weekday from text date

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
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Weekday from text date

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



  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Weekday from text date

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




  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Weekday from text date

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!

  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default Weekday from text date

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


Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Text Form Date Mary Fetsch Microsoft Word Help 6 February 25th 10 03:55 PM
Text Box & Date Updating osbornauto Microsoft Word Help 2 December 9th 09 07:14 PM
Date changes from the desired date to current date when printing gsf1 Microsoft Word Help 1 April 9th 09 06:49 PM
Auto text date. RHellams Microsoft Word Help 1 April 25th 08 08:22 AM
Convert text to date [email protected] Microsoft Word Help 2 January 17th 06 05:47 PM


All times are GMT +1. The time now is 01:50 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"