CODEWITHSUNDEEP
exceldate
user3068093
user3068093

Reputation: 37

Convert quarter-year to MM/DD/YYYY in Excel

How to convert this quarter-year format into MM/DD/YYYY in Excel where quarter will convert to first day of quarter. For example, Q1-2014 to 1/1/2014, Q2-2015 to 4/1/2015, Q3-2016 to 7/1/2016, Q4-2017 to 10/1/2017

Upvotes: 3

Views: 15770

Answers (2)

teylyn
teylyn

Reputation: 35915

Try

=DATE(RIGHT(A1,4),(MID(A1,2,1)*3)-2,1)

That will return a date. Format to display in whatever date format you want.

Upvotes: 10

dgorti
dgorti

Reputation: 1240

Here is a formula you could use

=DATE(MID(A1,4,4),IF(MID(A1,1,2)= "Q1", 1, IF(MID(A1, 1, 2) = "Q2", 4, IF(MID(A1,1,2)="Q3", 7, 10))), 1)

Assumptions The format is Q[1/2/3/4]-YYYY

Formula Take the last 4 digits as Year Take Month as 1 if Q1 else 3 if month is Q3,... Take Date as 1

Upvotes: 1

Related Questions