This function can be used to create a date from three integers representing the day, the month and the year.
The function takes the following parameters:
Day As Integer
Month As Integer
Year As Integer
This function is ideal for creating workbooks that have columns representing different periods. When used in conjunction with the FirstOfNextMonth function and Microsoft’s standard date functions it allows the whole workbook to calculate all dates based on one cell containing a date. For example, if the company’s year end is 31/10/2015, and a workbook is to have four columns of data being the four quarters of the financial year, the start and end date of each quarter can be calculated as follows:
Q1start = BuildDate(Day(31/10/2015+1),Month(31/10/2015+1),Year(31/10/2015)-1)
Q2start = FirstOfNextMonth(FirstOfNextMonth (FirstOfNextMonth (Q1start)))
Q3tart = FirstOfNextMonth(FirstOfNextMonth (FirstOfNextMonth (Q2start)))
Q4start = FirstOfNextMonth(FirstOfNextMonth (FirstOfNextMonth (Q3start)))
Q1end = Q2start - 1
Q2end = Q3start - 1
Q3end = Q4start - 1
Q1end = 31/10/2015