Reading the input data from a merged excel file:

yyyy-mm-dd: 2008-03-01

yyyymmdd: 20080301

Setting the year, month, and day values of the input date:

Computing the year, month, and day values of the previous date:

Computing the previous day’s date value of any given input date with MS Word field formulas

Emin Gabrielyan

2008-09-04

Switzernet

This document [doc] applies Word field formulas for computing date value of a previous day of a given input date [ch1], [ch2]. In this version, the input dates are read from a merged excel document [xls] containing numerous randomly generated dates.

For isolating the field formulas from the main document layout, the calculation formulas are located in a separated text box. The text box can be hidden anywhere in the document without affecting the main layout. For the final version you can minimize the size of the textbox to an invisible size, remove the comments in the text box, make its background transparent, and remove borders. To show the field formulas in the textbox (the green one is the true textbox), select its full content (Ctrl-A), right-click, and choose “Toggle Field Codes” option of the pop-up menu. Below is the screenshot of the textbox of this document where all formulas are unhidden (next to it, the green one, is the real textbox):

For testing locally, download the excel file [xls] with this doc file [doc] and merge them locally (Choose in main menu of the Word Document “View” - “Toolbars” - “Mail Merge”, then click on the “Open Data Source” icon for joining the excel file). The excel file [xls] contains 100 dates which are generated randomly.

The text below uses the first values of the dates read from the excel file and processed by the field formulas of the text box:

The input date and its previous date are below (in yyyy-MM-dd format):

2008-3-1

2008-2-29

The input date and its previous date are below (in “dddd, MMMM dd, yyyy” format):

Saturday, March 01, 2008

Friday, February 29, 2008

*   *   *

References:

http://office.microsoft.com/en-us/word/HP051862181033.aspx

http://office.microsoft.com/en-us/word/HP051861861033.aspx

http://switzernet.com/public/080902-layout-red-slip/

http://unappel.ch/public/080902-layout-red-slip/

http://www.addbalance.com/word/datefields1.htm

http://www.addbalance.com/word/datefields2.htm

http://forums.techguy.org/business-applications/265813-creating-field-word-excel-formula.html

Using MS Word field formulas to compute the previous date of a given input date (without merging with an excel file):

http://switzernet.com/public/080904-word-field-date-calc/

http://unappel.ch/public/080904-word-field-date-calc/

http://google.ch/search?q=Creating+a+field+in+Word+with+an+Excel+formula

http://google.ch/search?q=word+field+formulas+for+dates

Computing previous days’ values of a set of input dates with MS Word field formulas (this document):

http://switzernet.com/public/080904-word-field-process-dates/

http://unappel.ch/public/080904-word-field-process-dates/

*   *   *

Copyright © 2008 Switzernet