A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Weekday from text date



 
 
Thread Tools Display Modes
  #1  
Old May 16th, 2010, 08:29 AM posted to microsoft.public.word.mailmerge.fields
Walter Briscoe
external usenet poster
 
Posts: 62
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  
Old May 16th, 2010, 08:52 AM posted to microsoft.public.word.mailmerge.fields
Graham Mayor
external usenet poster
 
Posts: 18,297
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  
Old May 16th, 2010, 10:08 AM posted to microsoft.public.word.mailmerge.fields
Graham Mayor
external usenet poster
 
Posts: 18,297
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  
Old May 17th, 2010, 12:39 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
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  
Old May 18th, 2010, 10:42 AM posted to microsoft.public.word.mailmerge.fields
macropod[_2_]
external usenet poster
 
Posts: 2,402
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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.