There are several ways to achieve this.
The easiest assumes that the start & end dates are consistent.
- The "between" dates should be in two cells. The "start" date is set to 2nd of the current month
=DATE(YEAR( NOW() ), MONTH( NOW() ), 2)
, the "end" date to the 2nd of the current month +1 =DATE(YEAR( NOW() ), MONTH( NOW() ) +1, 2)
- The "current" date is then simply set to the "start" date.
However, you need to "fix" these dates when the spreadsheet is saved. Assuming that the "current" date is in cell B3 and the 2 between dates are in cells . You can use something like:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("B3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("B5:C5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End Sub
In the ThisWorkbook
code module. This simply does a copy/past values on the cells so that the formula is replaced by the dates.
Oops, one addition is required to this. The code I've given is a little two simplistic since you would not be able to save the template so you need to wrap the given code in something that prevents it from triggering when editing the template itself. Possibly the easiest way is to use a naming convention on the files. Perhaps the template has the text "TEMPLATE " at the start of the file name.