SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-643657%22%20slang%3D%22en-US%22%3ECalulate%20month%20%2Fyear%20on%20accounting%20calender%20when%20the%20next%20month%20starts%20within%20the%20previous%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643657%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20All%3C%2FP%3E%3CP%3EI%20have%20a%20column%20in%20excel%20with%20dates%20but%20need%20to%20calculate%20the%20accounting%20month.%20So%20for%20the%20calendar%2C%26nbsp%3BFeb%20-1-%26nbsp%3B2017%20to%20Feb-5-2017%20the%20date%20column%20i%20need%20it%20to%20say%20Jan-2017%2C%20since%20its%20fall%20within%20the%20accounting%20calendar%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F115929iEBEB99601AE26601%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22calender.jpg%22%20title%3D%22calender.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-643657%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644693%22%20slang%3D%22en-US%22%3ERe%3A%20Calulate%20month%20%2Fyear%20on%20accounting%20calender%20when%20the%20next%20month%20starts%20within%20the%20previous%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644693%22%20slang%3D%22en-US%22%3EHow%20do%20you%20determine%20your%20%E2%80%9Caccounting%20month%E2%80%9D%3F%20It%20seems%20that%20it%20starts%20at%20the%20first%20Monday%20after%20the%20first%20Sunday%20of%20the%20current%20month%20and%20ends%20at%20the%20first%20Sunday%20of%20the%20following%20month.%20Please%20clarify.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644983%22%20slang%3D%22en-US%22%3ERe%3A%20Calulate%20month%20%2Fyear%20on%20accounting%20calender%20when%20the%20next%20month%20starts%20within%20the%20previous%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644983%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20accounting%20month%20start%20on%20a%20Monday%20current%20month%20and%20ends%20at%20the%20first%20Sunday%20of%20the%20following%20month.%26nbsp%3BIt%20takes%20into%20account%20the%20last%20full%20week%20before%20going%20into%20the%20next%20month%20for%20it%20to%20be%20counted%20in%20the%20previous%20month%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-645830%22%20slang%3D%22en-US%22%3ERe%3A%20Calulate%20month%20%2Fyear%20on%20accounting%20calender%20when%20the%20next%20month%20starts%20within%20the%20previous%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-645830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347847%22%20target%3D%22_blank%22%3E%40bu965%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20my%20understanding%20of%20your%20explanation%2C%20%22Accounting%20Month%22%20starts%20on%20the%20First%20Monday%20of%20each%20month.%20Thus%2C%20the%20formula%20in%20B2%20of%20the%20attached%20file%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAcctgMonth%3C%2FP%3E%3CP%3EThe%20foregoing%20is%20a%20named%20formula%2C%20the%20details%20of%20which%20are%20as%20follows%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EAcctgMonth%3C%2FTD%3E%3CTD%3E%3DCHOOSE((CellLeft%26gt%3B%3DFirstMonLastMonth)%2B(CellLeft%26gt%3B%3DFirstMonThisMonth)%2B(CellLeft%26gt%3B%3DFirstMonNextMonth)%2CLastMonth%2CThisMonth%2CNextMonth)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECellLeft%3C%2FTD%3E%3CTD%3E%3D!C3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFirstMonLastMonth%3C%2FTD%3E%3CTD%3E%3DEOMONTH(CellLeft%2C-2)%2B1%2B7-WEEKDAY(EOMONTH(CellLeft%2C-2)%2B1-2)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFirstMonNextMonth%3C%2FTD%3E%3CTD%3E%3DEOMONTH(CellLeft%2C0)%2B1%2B7-WEEKDAY(EOMONTH(CellLeft%2C0)%2B1-2)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFirstMonThisMonth%3C%2FTD%3E%3CTD%3E%3DEOMONTH(CellLeft%2C-1)%2B1%2B7-WEEKDAY(EOMONTH(CellLeft%2C-1)%2B1-2)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELastMonth%3C%2FTD%3E%3CTD%3E%3DTEXT(EOMONTH(CellLeft%2C-1)%2C%22mmm-yyyy%22)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENextMonth%3C%2FTD%3E%3CTD%3E%3DTEXT(EOMONTH(CellLeft%2C1)%2C%22mmm-yyyy%22)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThisMonth%3C%2FTD%3E%3CTD%3E%3DTEXT(CellLeft%2C%22mmm-yyyy%22)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-645848%22%20slang%3D%22en-US%22%3ERe%3A%20Calulate%20month%20%2Fyear%20on%20accounting%20calender%20when%20the%20next%20month%20starts%20within%20the%20previous%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-645848%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347847%22%20target%3D%22_blank%22%3E%40bu965%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20my%20understanding%20of%20your%20explanation%2C%20%22Accounting%20Month%22%20starts%20on%20the%20First%20Monday%20of%20each%20month.%20Thus%2C%20the%20formula%20in%20B2%20of%20the%20attached%20file%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAcctgMonth%3C%2FP%3E%3CP%3EThe%20foregoing%20is%20a%20defined%20formula%2C%20the%20details%20of%20which%20are%20as%20follows%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EAcctgMonth%3C%2FTD%3E%3CTD%3E%3DCHOOSE((CellLeft%26gt%3B%3DFirstMonLastMonth)%2B(CellLeft%26gt%3B%3DFirstMonThisMonth)%2CLastMonth%2CThisMonth)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECellLeft%3C%2FTD%3E%3CTD%3E%3D!C3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFirstMonLastMonth%3C%2FTD%3E%3CTD%3E%3DEOMONTH(CellLeft%2C-2)%2B1%2B7-WEEKDAY(EOMONTH(CellLeft%2C-2)%2B1-2)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFirstMonThisMonth%3C%2FTD%3E%3CTD%3E%3DEOMONTH(CellLeft%2C-1)%2B1%2B7-WEEKDAY(EOMONTH(CellLeft%2C-1)%2B1-2)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELastMonth%3C%2FTD%3E%3CTD%3E%3DTEXT(EOMONTH(CellLeft%2C-1)%2C%22mmm-yyyy%22)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThisMonth%3C%2FTD%3E%3CTD%3E%3DTEXT(CellLeft%2C%22mmm-yyyy%22)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-645860%22%20slang%3D%22en-US%22%3ERe%3A%20Calulate%20month%20%2Fyear%20on%20accounting%20calender%20when%20the%20next%20month%20starts%20within%20the%20previous%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-645860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347847%22%20target%3D%22_blank%22%3E%40bu965%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20my%20understanding%20of%20your%20explanation%2C%20%22Accounting%20Month%22%20starts%20on%20the%20First%20Monday%20of%20each%20month.%20Thus%2C%20the%20formula%20in%20B2%20of%20the%20attached%20file%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAcctgMonth%3C%2FP%3E%3CP%3EThe%20foregoing%20is%20a%20named%20formula%2C%20the%20details%20of%20which%20are%20as%20follows%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EAcctgMonth%3C%2FTD%3E%3CTD%3E%3DIF(CellLeft%3CFIRSTMONTHISMONTH%3E%3C%2FFIRSTMONTHISMONTH%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECellLeft%3C%2FTD%3E%3CTD%3E%3D!C3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFirstMonThisMonth%3C%2FTD%3E%3CTD%3E%3DEOMONTH(CellLeft%2C-1)%2B1%2B7-WEEKDAY(EOMONTH(CellLeft%2C-1)%2B1-2)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELastMonth%3C%2FTD%3E%3CTD%3E%3DTEXT(EOMONTH(CellLeft%2C-1)%2C%22mmm-yyyy%22)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThisMonth%3C%2FTD%3E%3CTD%3E%3DTEXT(CellLeft%2C%22mmm-yyyy%22)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-650296%22%20slang%3D%22en-US%22%3ERe%3A%20Calulate%20month%20%2Fyear%20on%20accounting%20calender%20when%20the%20next%20month%20starts%20within%20the%20previous%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-650296%22%20slang%3D%22en-US%22%3EThanks%20it%20works%20fine%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-650977%22%20slang%3D%22en-US%22%3ERe%3A%20Calulate%20month%20%2Fyear%20on%20accounting%20calender%20when%20the%20next%20month%20starts%20within%20the%20previous%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-650977%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome.%3C%2FLINGO-BODY%3E
bu965
New Contributor

 

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.

 

calender.jpg

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.

@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

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")
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies