Forum Discussion
Solved
Calulate month /year on accounting calender when the next month starts within the previous month
Hi All
I have a column in excel with dates but need to calculate the accounting month. So for the calendar, Feb -1- 2017 to Feb-5-2017 the date column i need it to say Jan-2017, since its fall within the accounting calendar month.
From my understanding of your explanation, "Accounting Month" starts on the First Monday of each month. Thus, the formula in B2 of the attached file is:
=AcctgMonth
The foregoing is a named formula, the details of which are as follows:
AcctgMonth =IF(CellLeft<FirstMonThisMonth,LastMonth,ThisMonth) CellLeft =!C3 FirstMonThisMonth =EOMONTH(CellLeft,-1)+1+7-WEEKDAY(EOMONTH(CellLeft,-1)+1-2) LastMonth =TEXT(EOMONTH(CellLeft,-1),"mmm-yyyy") ThisMonth =TEXT(CellLeft,"mmm-yyyy")
5 Replies
- How do you determine your “accounting month”? It seems that it starts at the first Monday after the first Sunday of the current month and ends at the first Sunday of the following month. Please clarify.
From my understanding of your explanation, "Accounting Month" starts on the First Monday of each month. Thus, the formula in B2 of the attached file is:
=AcctgMonth
The foregoing is a named formula, the details of which are as follows:
AcctgMonth =IF(CellLeft<FirstMonThisMonth,LastMonth,ThisMonth) CellLeft =!C3 FirstMonThisMonth =EOMONTH(CellLeft,-1)+1+7-WEEKDAY(EOMONTH(CellLeft,-1)+1-2) LastMonth =TEXT(EOMONTH(CellLeft,-1),"mmm-yyyy") ThisMonth =TEXT(CellLeft,"mmm-yyyy")
