Calculating fiscal month number in Excel

This has come via this post at Mr Excel. and a response provided by Ron Coderre. Assuming the fiscal year starts in April, the formula below will give you the fiscal month. =MONTH(EDATE(A1,9)) The value of '9' is derived by the months remaining in the year that are the new financial year. In this case … Continue reading Calculating fiscal month number in Excel

Date tricks in Excel – Latest Month, and preceeding 12

Assuming you have your data formatted as a table (if not, why not) then you can use the following technique to see if it is the most current month for that set of date or if it is in the most recent n number of months. For this example we assume that you have a … Continue reading Date tricks in Excel – Latest Month, and preceeding 12

Automatically Reporting on Previous Calendar Month

This little snippet is used in a filter and automatically looks at the previous calendar month. cast([Sales Team].[Client Contacts].[Start Date],date) between _first_of_Month(_add_months(CURRENT_DATE,-1)) and _last_of_month(_add_months(CURRENT_DATE,-1)) If the date is left as a timestamp then the last of the month is 2013-04-30 00:00 which means that a client contact that occurs at 9am would not be picked … Continue reading Automatically Reporting on Previous Calendar Month