Compact formulas for computing the date of the previous day

We already presented field formulas for computing the previous day of any given date [080904-word-field-date-calc], [ch1], [ch2], [080904-word-field-process-dates], [ch1], [ch2]. The computation is done with three simple formulas for computing the year =aay-and(aam=1,aad=1), the month =mod(aam-1+12-(aad=1),12)+1, and the day =if(aad>1,aad-1,if(bbm=2,if(mod(bby,4)=0,29,28),30+mod(bbm+(bbm>=8),2))) values of the previous day. The formulas are Excel compatible (we use functions mod, and, and if). Joined is an excel file using exactly the same formulas [xls]. The formulas can be copy-pasted back and forth between ms-word and excel. An operational web version of the excel sheet is also provided [htm]. Change the input cell values then use tab key and shift-tab to switch between input cells and to update the output cells. Below is the screenshot of the toggled field codes followed by the field codes themselves:

[doc]

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

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

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

The version presented in this section is more compact and accurate than the versions presented in previous documents [080904-word-field-date-calc], [ch1], [ch2], [080904-word-field-process-dates], [ch1], [ch2]. The leap year computation is not complete when it concerns the century changes.

Mysteries of Word field formulas when using variable names ending with a number

Do not use digits at the end of variable names when using field formulas. In formula {=a1+1} the reference to variable “a1” is replaced by 0, while the reference to variable “a1val” is treated as expected {=a1val+1}. Curved brackets {} replace the variables with their values and may avoid errors if a variable name ending with a number must be used at any cost {={a1}+1}.

Below is the screenshot of the toggled field codes followed by the field codes themselves:

[doc]

Setting the value of a1val to 100:

Setting the value of a1 to 200:

Computation with a1val: 101

Computation with {a1val}: 101

Computation with a1: 1

Computation with {a1}: 201

The field codes demonstrate that the result of {=a1+1} formula is 1 instead of 101. Though two variables, a1 and a1val, are defined as expected:

Making arrays by storing the names of variables in a variable:

Variable names can be computed on the fly in the assignment operation {set var val} and when accessing and displaying numerical values with formulas {=var}. Variables storing variable name is an alternative to arrays for working with indexed numerical values. Below is the screenshot of the toggled field codes followed by the field codes themselves:

[doc]

Setting i to 0

Setting v to 1

Setting a0val to 1

Setting a1val to 3

Setting a2val to 9

Setting i to 0

Variable a0val contains 1

Variable a1val contains 3

Variable a2val contains 9

As expected, after updating the field codes you can see the defined variables (bookmarks):

To see bookmarks press Alt-I-K. You have to delete all bookmarks, select and update the field codes, and then view the bookmarks again in order to see only those variables which are defined by the updated field codes.

The substitution of the variable name by another variable containing that name does work only for numerical formulas {=…}. Variable name cannot be substituted within the field operator {var}.

Computing the first days of a number of past months

Below is the screenshot of the toggled field codes for computing the dates of first days of past five months. The screenshot is followed by the field codes themselves: