May 24 2019 05:38 AM
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.
May 24 2019 10:47 AM
May 24 2019 11:50 AM
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
May 24 2019 11:04 PM
SolutionFrom 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") |
May 27 2019 06:23 AM
May 27 2019 06:18 PM
May 24 2019 11:04 PM
SolutionFrom 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") |