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 wi...
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")
The accounting month start on a Monday current month and ends at the first Sunday of the following month. It takes into account the last full week before going into the next month for it to be counted in the previous 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") |