• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 65
  • Last Modified:

I have yyyym format in excel, I want previous month in the same format

So if my date is 201701, I want the next column as 201612(previous month) Also if my date is 201701, I want the quarter also as 201703 in another column. Thanks!
Month  Prev_Month  Quarter
201701 201612 201703
This is the output I want. Preferably in excel.
0
Nupur Jain
Asked:
Nupur Jain
1 Solution
 
AlanConsultantCommented:
Hi,

If your current month is in A1:

Previous Month = VALUE(TEXT(DATE(LEFT(A2,4),RIGHT(A2,2),0),"YYYYMM"))

Quarter = VALUE(TEXT(DATE(LEFT(A2,4),RIGHT(A2,2)+MOD(-MOD(RIGHT(A2,2),3)+3,3)+1,0),"YYYYMM"))

See attached.

Alan.
EE-29076689-Dates.xls
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Assuming your date is in A2, then

If 201701 is a number not a real date formatted as yyyymm, try this...
In B2
=TEXT(EOMONTH(DATEVALUE(LEFT(A2,4)&"-"&RIGHT(A2,2)&"-1"),-1),"yyyymm")

Open in new window

In C2
=TEXT(EOMONTH(DATEVALUE(LEFT(A2,4)&"-"&RIGHT(A2,2)&"-1"),2),"yyyymm")

Open in new window


If 201701 is output from a real date formatted as yyyymm, try this...
in B2
=TEXT(EOMONTH(A2,-1),"yyyymm")

Open in new window

In C2
=TEXT(EOMONTH(A2,2),"yyyymm")

Open in new window

0
 
Nupur JainAuthor Commented:
Thanks that helped!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now