Changing "General"data (Month, Quarter and Year) to a "Date" format other than 09 July 1905!

[Brazen]

Good Afternoon,

Hopefully someone can help me with this issue I have an Excel Document similar to below, with the fields all showing their format as "General" as this is how the data is sent to me.

[TABLE="width: 271"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Quarter[/TD]
[TD]Year[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Q1[/TD]
[TD]2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Q1[/TD]
[TD]2017[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Q1[/TD]
[TD]2017[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Q2[/TD]
[TD]2018[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Q2[/TD]
[TD]2018[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]



[/TD]
[TD][/TD]
[TD]




[/TD]
[/TR]
</tbody>[/TABLE]
I am looking to:
- Find a way to change the month from 1/2/3/4.... to Jan/Feb/Mar etc.
- Have Q1 recognized as a period of time
- Have Year (2017/2018 etc) show as 2017/2018 but formatted as a year

The reason for doing this is so that I can start looking at Power BI reports showing MoM, QoQ and YoY data. I didnt publish this in the Power BI section as I believe this is an Excel query.

Many Thanks in advance for any help you can provide!

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F
1	Month	Quarter	Year	Units
2	1	Q1	2017	1		1/1/2017
3	1	Q1	2017	4		1/1/2017
4	2	Q1	2017	6		2/1/2017
5	2	Q2	2018	10		5/1/2018
6	1	Q2	2018	4		4/1/2018
Sheet13

Cell Formulas
Range	Formula
F2	=DATE(C2,A2+LOOKUP(B2,{"Q1","Q2","Q3","Q4";0,3,6,9}),1)

 

[sheetspread]

=A2+3*(RIGHT(B2,1)-1) also gets you the month (can be used in Power BI)

 

[Brazen]

Thank you very much! I will take a look and see if I can get this to work on my end

 

[Brazen]

Hi Sheetspread, so I have tried the above formula and it is just what I wanted... however...

As soon as it gets to Month "4", Quarter "Q2" it posts 01/07/Year rather than, 01/04/Year.

Any ideas on this?

 

[sheetspread]

Does Month mean month of the year or month of the quarter? Row 4 in your original post says Month 2 Q2, which seems to be the second month of the second quarter (May). The second month of the year is February, but that's in Q1.

Since I assumed month of the quarter, Month 4 Q2 returns 1 month after the third month of quarter 2 (July), rather than the 4th month of the year (April).

 

[Brazen]

Month in this case means month of the year. the quarter is just included to add another time period with which to analyse the data.

 

[sheetspread]

Then:

Excel 2010
	A	B	C	D	E	F
1	Month	Quarter	Year	Units
2	1	Q1	2017	1		1/1/2017
3	1	Q1	2017	4		1/1/2017
4	2	Q1	2017	6		2/1/2017
5	2	Q2	2018	10		2/1/2018
6	1	Q2	2018	4		1/1/2018
Sheet13

Cell Formulas
Range	Formula
F2	=DATE(C2,A2,1)

Remember, if you print 2017 alone in a cell, and format as a date instead of regular number, etc. it will read 7/9/1905 (# of days since 1/0/1900).

 

[Brazen]

Thank you!!! this appears to have worked!