Indexed time

JeanM01

New Member
Joined
Jun 10, 2015
Messages
9
Hi everyone,

I am trying to write a code or function that will index the the time. I have data ranging from January 2012 to December 2014. I'd like to place in a column nearby it that if it's Jan-2012 then give me 1, if Feb-2012 then give me 2,...,Dec-2014 give me 36. Is there a way to do this? Thanks in advance!

Jean
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Hi, Just a guess..

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">01-Jan-2012</td><td style="text-align: right;background-color: #FFFF00;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">13-Jan-2012</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">25-Jan-2012</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">06-Feb-2012</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">18-Feb-2012</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">01-Mar-2012</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">13-Mar-2012</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">25-Mar-2012</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">06-Apr-2012</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">18-Apr-2012</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">30-Apr-2012</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">12-May-2012</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">24-May-2012</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">05-Jun-2012</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">17-Jun-2012</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">29-Jun-2012</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">11-Jul-2012</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">23-Jul-2012</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">04-Aug-2012</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">16-Aug-2012</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">28-Aug-2012</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">09-Sep-2012</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">21-Sep-2012</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">03-Oct-2012</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">12-Dec-2014</td><td style="text-align: right;;">36</td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Basesheet</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><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: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=MONTH(<font color="Blue">A2</font>)*(<font color="Blue">YEAR(<font color="Red">A2</font>)-YEAR(<font color="Red">$A$2</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />
 

JeanM01

New Member
Joined
Jun 10, 2015
Messages
9
Hi, Just a guess..

Excel 2012
AB
201-Jan-20121
313-Jan-20121
425-Jan-20121
506-Feb-20122
618-Feb-20122
701-Mar-20123
813-Mar-20123
925-Mar-20123
1006-Apr-20124
1118-Apr-20124
1230-Apr-20124
1312-May-20125
1424-May-20125
1505-Jun-20126
1617-Jun-20126
1729-Jun-20126
1811-Jul-20127
1923-Jul-20127
2004-Aug-20128
2116-Aug-20128
2228-Aug-20128
2309-Sep-20129
2421-Sep-20129
2503-Oct-201210
2612-Dec-201436

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Basesheet

Worksheet Formulas
CellFormula
B2=MONTH(A2)*(YEAR(A2)-YEAR($A$2)+1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
This formula won't work: Jan 2013 =13 but your formula 1*(2) =2
But I did find another function
=MATCH(Date, array)
Thank you for answering!
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You could try using DATEDIF, e.g. with date in A2

=DATEDIF(DATE(2012,1,1),A2,"m")+1
 

Watch MrExcel Video

Forum statistics

Threads
1,122,649
Messages
5,597,368
Members
414,139
Latest member
okela0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top