Looking to set up Quarters, Starting in Feb not Jan

shophoney

Board Regular
Joined
Jun 16, 2014
Messages
219
Hi, I'm looking to create Quarters starting in Feb-Mar, Apr-Jun...

I have a calendar table, what formula would be best to calculate that? And should it be 2018-Q1, 2018-Q2...?

Thanks
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
Assuming date in A1

=YEAR(EDATE(A1,-1))&"-Q"&-INT(-MONTH(EDATE(A1,-1))/3)

Copy down.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">1/1/2018</td><td style=";">2017-Q4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">2/1/2018</td><td style=";">2018-Q1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">3/1/2018</td><td style=";">2018-Q1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">4/1/2018</td><td style=";">2018-Q1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">5/1/2018</td><td style=";">2018-Q2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">6/1/2018</td><td style=";">2018-Q2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">7/1/2018</td><td style=";">2018-Q2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">8/1/2018</td><td style=";">2018-Q3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">9/1/2018</td><td style=";">2018-Q3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">10/1/2018</td><td style=";">2018-Q3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">11/1/2018</td><td style=";">2018-Q4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">12/1/2018</td><td style=";">2018-Q4</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A1,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A1,-1</font>)</font>)/3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A2,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A2,-1</font>)</font>)/3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A3,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A3,-1</font>)</font>)/3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A4,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A4,-1</font>)</font>)/3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A5,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A5,-1</font>)</font>)/3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B6</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A6,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A6,-1</font>)</font>)/3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B7</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A7,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A7,-1</font>)</font>)/3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A8,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A8,-1</font>)</font>)/3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A9,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A9,-1</font>)</font>)/3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B10</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A10,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A10,-1</font>)</font>)/3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B11</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A11,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A11,-1</font>)</font>)/3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">=YEAR(<font color="Blue">EDATE(<font color="Red">A12,-1</font>)</font>)&"-Q"&-INT(<font color="Blue">-MONTH(<font color="Red">EDATE(<font color="Green">A12,-1</font>)</font>)/3</font>)</td></tr></tbody></table></td></tr></table><br />
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
Oh sorry, I didn't see that this wasn't in the Excel forum.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,375
Messages
5,468,238
Members
406,575
Latest member
Joe00

This Week's Hot Topics

Top