Graham Mayor

... helping to ease the lives of Microsoft Word users.


Many people access the material from this web site daily. Most just take what they want and run. That's OK, provided they are not selling on the material as their own; however if your productivity gains from the material you have used, a donation from the money you have saved would help to ensure the continued availability of this resource. Click the appropriate button above to access PayPal.

Insert a future (or past) date add-in for Word

It is a common requirement in documents to insert dates that occur a number of days after an initial date. This is covered in some depth in the companion page linked from the column on the right.

To broaden the range of handy add-in functions available from this site, I thought it would be useful to put together an add-in that used the VBA version featured on that page, enhanced to allow for weekends and holidays to be included in the count.

Easter holidays (Roman and Orthodox) and weekends are hard coded into the add-in. Other holidays are user-programmable to account for local and national holidays, by entering the holidays in a pair of tables in the add-in template itself. To access these tables it is necessary to open the template for editing in Word and make the changes.

Variable holidays are those which occur on a particular day of the month, but in which the date itself varies. e.g. The US Holiday 'Thanksgiving' occurs on the 4th Thursday in November. The above table allow such dates to be handled. The table also accepts the word 'Last' (not case sensitive) for the last particular day of the month e.g. The U.S. Memorial Holiday is the last Monday in May.

Where date numbers are incorrectly entered the holiday is ignored.

Fixed holidays are those that occur on the same date each year, for example New Year's Day is always on the 1st January.

The add-in is activated from a button on the Insert tab of the ribbon:

After the usual disclaimer, text the add-in displays a userform from which the basic selections are made. The options should be self explanatory. The initial date is today's date.

Click the Pick Date button and a date picker is presented. Click the date in the calendar to replace the date in the Initial Date box.

Thanks to Trevor Eyre for developing the Date Picker function.  For your own projects, the Date Picker userform and associated code may be downloaded here.

 

All calculations are based on the initial value and the value of the number of days entered to count on (or back) from the initial date value.

Check the 'Exclude Weekends' check box to enable the three options that relate to how weekends are handled.

By default the date is inserted at the current cursor position, but you may prefer to insert the date at a pre-defined bookmark location anywhere in the current document, or replace a bookmarked date with a revised date. By checking the 'Check to insert date at a named bookmark' check box you will be presented with a text box to enter the name of the bookmark in question.

By default the bookmark name 'bmDate' is used, but this can be changed to suit personal preferences and the change stored in the template.

If the named bookmark is missing from the document when this choice is selected, nothing is inserted into the document by the add-in.

This option may also be used to insert a date in a suitable content control ('Date', 'Text', 'Rich Text') if that control is included in the bookmarked location. If more than one content control is in that bookmarked location, only the first such control is processed.

Date Examples - all using an initial date of 14 July 2015

  • Tuesday, 14 July 2015 – Insert current date i.e. 0 days added
  • Thursday, 9 July 2015 – Subtract 5 days (does not fall at weekend)
  • Friday, 10 July 2015 – Subtract 3 days (include weekends – Friday option)
  • Monday, 13 July 2015 – Subtract 3 days (include weekends – Monday option)
  • Friday, 17 July 2015 – Add 5 days (include weekends – Friday option)
  • Monday, 20 July 2015 – Add 5 days (include weekends – Monday option)
  • Friday, 17 July 2015 – Add 3 days (does not fall at weekend)

Note that if you are inserting today's date i.e. not adding or subtracting a number of days from the current date, and today falls at the weekend, checking the 'Exclude Weekends' check box will still move the date to the previous Friday or following Monday, according to the setting choice.

Holidays

Checking the 'Exclude Holidays' check box enables another tabbed page where you may choose options for Easter (Roman and/or Orthodox) which employ algorithms to calculate Easter for any year.. You may also choose to include holidays entered in the tables described at the start of this page.

The calculated dates is inserted at the current cursor position..

Date formats should reflect users' local Regional settings.

All settings are stored for ease of re-use, with the exception of the Initial Date, which is always today's date.

Click 'Restore Defaults' to clear the stored data.

 

- Click here to download the add-in

 

Share

 

Insert Future Dates with VBA

This page features an add-in which is loosely based on the code at the end of the companion page 'Insert a date other than today's date' but much enhanced to accommodate holidays and weekends and served up in an accessible format for users uncomfortable with VBA programming.