Indexed time

[JeanM01]

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

 

[FormR]

Hi, Just a guess..

Excel 2012
	A	B
2	01-Jan-2012	1
3	13-Jan-2012	1
4	25-Jan-2012	1
5	06-Feb-2012	2
6	18-Feb-2012	2
7	01-Mar-2012	3
8	13-Mar-2012	3
9	25-Mar-2012	3
10	06-Apr-2012	4
11	18-Apr-2012	4
12	30-Apr-2012	4
13	12-May-2012	5
14	24-May-2012	5
15	05-Jun-2012	6
16	17-Jun-2012	6
17	29-Jun-2012	6
18	11-Jul-2012	7
19	23-Jul-2012	7
20	04-Aug-2012	8
21	16-Aug-2012	8
22	28-Aug-2012	8
23	09-Sep-2012	9
24	21-Sep-2012	9
25	03-Oct-2012	10
26	12-Dec-2014	36
Basesheet

Cell Formulas
Range	Formula
B2	=MONTH(A2)*(YEAR(A2)-YEAR($A$2)+1)

 

[JeanM01]

Hi, Just a guess..

Excel 2012

	A	B
2	01-Jan-2012	1
3	13-Jan-2012	1
4	25-Jan-2012	1
5	06-Feb-2012	2
6	18-Feb-2012	2
7	01-Mar-2012	3
8	13-Mar-2012	3
9	25-Mar-2012	3
10	06-Apr-2012	4
11	18-Apr-2012	4
12	30-Apr-2012	4
13	12-May-2012	5
14	24-May-2012	5
15	05-Jun-2012	6
16	17-Jun-2012	6
17	29-Jun-2012	6
18	11-Jul-2012	7
19	23-Jul-2012	7
20	04-Aug-2012	8
21	16-Aug-2012	8
22	28-Aug-2012	8
23	09-Sep-2012	9
24	21-Sep-2012	9
25	03-Oct-2012	10
26	12-Dec-2014	36

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

Basesheet​

Worksheet Formulas Cell Formula 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]

You could try using DATEDIF, e.g. with date in A2

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

 

[FormR]

This formula won't work: Jan 2013 =13 but your formula 1*(2) =2

Opps! Good point, thanks for sharing.