Calulate month /year on accounting calender when the next month starts within the previous month

[bu965]

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.

[#2]

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.

[#3]

@Twifoo 

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

[#4 · Accepted solution]

@bu965 

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]

Thanks it works fine

[#6]

You’re welcome.