convert an eight-digit string to a date variable (Word97SR2)

Topic

New Reply

How to convert an eight-digit string to a date variable?

I have the following fields in my document. The {Ask} prompts me for an eight-digit string which I’d key in as 20010731.

I want the ‘week of’ field to print as a date format, so where you see InvNumb I really want something that assembles a date vale from my eight-digit value.

I used to have Week Of Time @ “dddd, MMMM dd, yyyy”}, but that gives me today’s date, the date on which I prepare the invoice, and i really want to create my invoices one after another, all on the same day, keying in an appropriate eight-digit string for each one.

{Ask InvNumb “Invoice Number”}
Week Of {InvNumb @ “dddd, MMMM dd, yyyy”}
Invoice Number {InvNumb @ “YYYYMMDD”}

Reply | Quote

Replies

you could use …

int(YYYYMMDD/10000) to return the year
mod(YYYYMMDD,100) to return the date
int(mod(YYYYMMDD,10000)/100) to return the month

to three separate variable and then go from there.

Reply | Quote

True; but it’s the “go from there” that has me stumped. I see no equivalent of a Date(yy,mm,dd) function to return a date-value, which I believe I need for the date formatting capabilities of the date field.

I can use the digit-portions of the 8-digit string to yield formatted numbers, but if I want also to produce a date as “Tuesday August 8th 2001” I need date values.

Reply | Quote

Try the VBA CDate conversion function. That will convert a date string to a date.

Reply | Quote

> VBA CDate conversion function

Thanks, Charlotte, indeed it will. I’m struggling, though, to obtain a Word-only solution.

With Timbo’s help I can now obtain the number of days difference between today’s date {DATE} and the invoice number.

If I could just add that difference to today’s date {DATE} I’d be set (because then I’d have a DATE adjusted, still a DATE, and could use the DATE formatting options within the field.

I’m also re-perusing Woody’s “TUG”.

Reply | Quote

[indent]

---

I’m struggling, though, to obtain a Word-only solution.

---

[/indent]Unless you enjoy pain, why? That’s what VBA is there for.

Reply | Quote

> That’s what VBA is there for.

I disagree. Starting with Woody’s references (The Underground Guide p106) Fields are seen as a powerful and versatile scheme of getting things done within the document.

I enjoy VBA (I am, after all, British by birth) yet I balk at having VBA code that will have to be invoked by the user to do a simple calculation. Especially as Word’s Field calculations seem so darn good.

My original posting in this thread was effectively asking if I’d overlooked something; apparently not. There appears to be no string-to-date conversion as there is in Excel and several other “languages”.

I can do it in VBA in my head while I’m typing this. So odd that it was left out of the Fields arsenal.

Reply | Quote

If you use the ‘int’ and ‘mod’ functions suggested by Timbo, and then enclose all these fields in a ‘quote’ field, with the date formatting at the end, does this not give you everything you need within Word?
see attached,

Oli

Reply | Quote

Oli! You have set the keystone in place. Thank you. I felt sure it ought be possible in Field codes. Not as elegant as a ConvDate field, but more versatile.

I have attached my vserion of the document and pasted the explanations here.

ask field

Invoice Number: 20010828

Year Portion: 2001

Month Portion: 8

Day portion: 28

Hard-coded: Invoice Date: Tuesday, August 28, 2001

Invoice Date: Tuesday, August 28, 2001

Explanation of underlying code:

{ ASK InvNumb “Type an 8-digit string”} This is the ask field. Select the entire document (Ctrl-A) and tap the F9 function key. You will be prompted for a string. Type in the eight digits 20010828 which represent Tuesday 28th August 2001 in a numeric form.

Invoice Number: { InvNumb } Your response to the {ASK} is stored in a bookmark variable “InvNumb”, from which we will extract our data. Here you see the 8-digit string being used purely as an identifier for the Invoice document.

Year Portion: { = int(InvNumb /10000)} Here is Timbo’s work. We extract the four-digit year portion.

Month Portion: {=int(mod(InvNumb,10000)/100) } Here is Timbo still at work. We extract the two-digit month portion.

Day portion:{ =mod(InvNumb,100) } The indefatigable Tmbo extracts the two-digit date portion.

Hard-coded: Invoice Date: { quote “2001 08 28” @ “dddd, MMMM dd, yyyy”} Here I have included an wayside rest stop. Oli chimed in with the use of the QUOTE field which I’d not met before. Word97’s help screens gave me some good ideas. Heh heh. Meanwhile, back at the ranch, you can experiment with this particular field. Try eliminating the spaces between the digit strings thus “20010828”. Try using slashes instead thus “2001/08/28”. Interesting, no? Try eliminating the double-quote characters. Aaaaargh!

Invoice Date:{ quote “{ = int(InvNumb /10000)} {=int(mod(InvNumb,10000)/100) } { =mod(InvNumb,100) }” @ “dddd, MMMM dd, yyyy”} OK. Here’s the result I wanted, and here I have it, thanks to Timbo and Oli. I took the hard-coded example above, and, being careful to retain those double-quote characters, I replaced the 4-digit and 2-digit strings with their correspoonding fields from Timbo’s work.

Voila!, as I used to say when I worked in Paris. Ole! In Alicante. Oli! in the Word forum.

Reply | Quote

> { ASK InvNumb “Type an 8-digit string”} This is the ask field. Select the entire document (Ctrl-A) and tap the F9 function key.

I should add that the Input box for the Ask field is awkward. The default behaviour on tapping the Enter key is to enter a CR/LF in the text box, rather than to confirm as if the OK button had been clicked.

This will trip you up from time to time. The side effects include awkward spacing in your document, because that extra CR/LF in your text box appears as extra paragraph spacing in your document wherever you make use of the entire bookmark (as in my example where I produce the Invoice Number as an 8-character string). You’ll see the extra spacing, and you’ll try to be rid of it by choosing Format, Paragraph, but to the best of my knowledge, there’s no way to be rid of it, execept by re-issuing the {Ask} (Ctrl-A, F9)

Reply | Quote

I have developed (attached) a scam^H^H^H^H scheme for converting an eight-character digit string to an ordinalized (?) date format.

I thought it couldn’t be done, but it can!

Wednesday is the source data for the mail merge
Thursday is a stepped set of exercises in field codes.
Just run the mail merge and watch the results.

I still have NOT solved my original problem of converting an eight-character dtring of digits to an internal date format.

Reply | Quote

Hi Chris,

Converting an 8-digit string into a date is really quite straightforward. My post 249902 shows how. Converting such a string the show the day# as an ordinal is a bit more effort, but doesn’t have to be as complex as your solution. See the attachment.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Macropod, thank you, and yes, you must recognise the source of my efforts.

I checked your new example for ordinals, and it looks/works great too, but as far as I can see we are still dealing with strings-of-characters.

These solutions implement a date-looking string from an 8-character date string (which is what the client wants), but I still can’t see a way of converting an 8-character string into a Date Serial value.

Some reflection is steering me towards the thought that perhaps I’m looking for the wrong concept. Word97 has DatePrinted, DateSaved etc., but no real date arithmetic; if it did, we’d see a DateValue such as is found in Excel97.

Again, thanks; your code provides an ordinal date string, which is going to look great on/in the document. I just wish we had a string-to-serial-date-value function.

Reply | Quote

(Edited by macropod on 01-May-04 13:25. Direct string to serial# conversion added)

Hi Chris,

Converting a date to a serial value is as ‘easy’ as:
{QUOTE{SET a{=INT((14-{DATE @ M})/12)}}{SET b{={DATE @ yyyy}+4800-a}}{SET c{={DATE @ M}+12*a-3}}{SET d{DATE @ d}}{SET DateValue{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045-2415019}}{DateValue # ,0}}
This uses the conversion of a Gregorian date to a Julian day number, as demonstrated in my DateCalc document, and simply deducts the Julian day number for 30/12/1899 (2,415,019) to get the integer-equivalent of the MS DateSerial value.

Now that you’ve got the field coding to convert a string to a date and to convert a date to a dateserial value, you shouldn’t find it too hard to combine the two. In fact, you don’t really need to convert the string to a date. For example, try:
{QUOTE{ASK Data Date @ YYYYMMdd}”The serial # for “{QUOTE “{=Data # “0000′-’00’-’00”}” @ “dddd, d

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

> Converting a date to a serial value is as ‘easy’ as:

Well, thank you. By the time I’d got this one embedded in Field codes, it was time for me to be embedded. I’ll tackle the edited appendage later today (Sunday!)

Reply | Quote

For easy field-code-to-text and back again conversions, see post 320421.

Reply | Quote

I don’t know how I missed seeing that post. I had one to convert code to text, but not the reverse. That’s fantastic…a real time saver!!

Reply | Quote

I don’t know how I missed seeing that post. I had one to convert code to text, but not the reverse. That’s fantastic…a real time saver!!

Reply | Quote

For easy field-code-to-text and back again conversions, see post 320421.

Reply | Quote

> Converting a date to a serial value is as ‘easy’ as:

Well, thank you. By the time I’d got this one embedded in Field codes, it was time for me to be embedded. I’ll tackle the edited appendage later today (Sunday!)

Reply | Quote

Macropod, thank you, and yes, you must recognise the source of my efforts.

I checked your new example for ordinals, and it looks/works great too, but as far as I can see we are still dealing with strings-of-characters.

These solutions implement a date-looking string from an 8-character date string (which is what the client wants), but I still can’t see a way of converting an 8-character string into a Date Serial value.

Some reflection is steering me towards the thought that perhaps I’m looking for the wrong concept. Word97 has DatePrinted, DateSaved etc., but no real date arithmetic; if it did, we’d see a DateValue such as is found in Excel97.

Again, thanks; your code provides an ordinal date string, which is going to look great on/in the document. I just wish we had a string-to-serial-date-value function.

Reply | Quote

Hi Chris,

Converting an 8-digit string into a date is really quite straightforward. My post 249902 shows how. Converting such a string the show the day# as an ordinal is a bit more effort, but doesn’t have to be as complex as your solution. See the attachment.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I have developed (attached) a scam^H^H^H^H scheme for converting an eight-character digit string to an ordinalized (?) date format.

I thought it couldn’t be done, but it can!

Wednesday is the source data for the mail merge
Thursday is a stepped set of exercises in field codes.
Just run the mail merge and watch the results.

I still have NOT solved my original problem of converting an eight-character dtring of digits to an internal date format.

Reply | Quote