Instead of using VLOOKUPs to return a month name like "AUGUST" or a formatted date such as August 31, 2015, using the Text and Date function in combination can achieve some very cool results.
The Date function requires three parameters, =Date(Year,Month,Day). When using Date(2015,8,31) - for 8/31/2015, if the cell is formatted as a number, Excel will return the value 42247. However if you wrap the Text function around this there are many options.
=Text(Date(2015,8,31) "MMM") returns AUG
=Text(Date(2015,8,31) "MMMM") returns AUGUST
=Text(Date(2015,8,31) "MMM YYYY") returns AUG 2015
=Text(Date(2015,8,31) "MM/DD/YY") returns 08/31/15
=Text(Date(2015,8,31) "M/DD/YY") returns 8/31/15
=Text(Date(2015,8,31) "MMMM DD, YYYY") returns August 31, 2015
When using these functions with BizInsight, the Year and Month that are used as parameters for the report, can be identified with a cell reference on your worksheet. All functions refer to B1 for the year, but use the values on row 3 or row 9 for the month. For this example the value 1 is being used for the Day.
Here are two examples; one that uses a calendar year and another that uses a fiscal year. On the fiscal year (June 30 year end) notice that the Month value in the function references a period number, but the value of six is added (D$9+6) to convert to the fiscal month, 1+6=July, 2+6=August).
