Generating Month from Date for Fiscal Calendar

[Christina4]

Greetings,

[TABLE="width: 227"]
<tbody>[TR]
[TD="class: xl64, width: 227"]My data is in the format MM/DD/YYYY and I want to generate the fiscal month and number. Example: Cell contains 7/5/2017 and formula generates 1-Jul. I have tried using the Month and Choose functions. I can get the output to yield the correct month number, but not the correct month name.Thank you for your help![/TD]
[/TR]
</tbody>[/TABLE]

Christina

 

[Drrellik]

right click on the cell and go to properties under either the date or under custom you can set it to appear however you want. MM/YY MM-YY MM-YYYY

HTH

 

[Christina4]

right click on the cell and go to properties under either the date or under custom you can set it to appear however you want. MM/YY MM-YY MM-YYYY

HTH

Thank you for your response. I must have been unclear. I need both columns of data. I am writing a macro. I need a formula to read the provided date and generate the correct fiscal month and corresponding number.

C D
1 Month Warrant Date
2 =MONTH(D2)-6 7/5/2017

I tried the above formula, but it yields 1-Jan. I need it to say 1-Jul.

 

[Dave Patton]

For Fiscal Year end June 30, July is month 1.

Excel 2010
	A	B	C	D	E	F
1	5-Jul-17	1-Jul	1	Jul	1	7
2	10-Jun-18	12-June	12	Jun	2	8
3					3	9
4					4	10
5					5	11
6					6	12
7					7	1
8					8	2
9					9	3
10					10	4
11					11	5
12					12	6
13
2a

Cell Formulas
Range	Formula
B1	=LOOKUP(MONTH(A1),$E$1:$F$12)&"-"&TEXT(A1,"mmm")
B2	=LOOKUP(MONTH(A2),{1,7;2,8;3,9;4,10;5,11;6,12;7,1;8,2;9,3;10,4;11,5;12,6})&"-"&TEXT(A2,"mmmm")
C1	=LOOKUP(MONTH(A1),E1:F12)
C2	=LOOKUP(MONTH(A2),E2:F13)
D1	=TEXT(A1,"mmm")
D2	=TEXT(A2,"mmm")

 

[Christina4]

For Fiscal Year end June 30, July is month 1.

Excel 2010

	A	B	C	D	E	F
1-Jul	Jul
12-June	Jun

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]5-Jul-17[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10-Jun-18[/TD]

[TD="align: right"]12[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

2a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=LOOKUP(MONTH(A1),$E$1:$F$12)&"-"&TEXT(A1,"mmm")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=LOOKUP(MONTH(A1),E1:F12)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=TEXT(A1,"mmm")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=LOOKUP(MONTH(A2),{1,7;2,8;3,9;4,10;5,11;6,12;7,1;8,2;9,3;10,4;11,5;12,6})&"-"&TEXT(A2,"mmmm")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=LOOKUP(MONTH(A2),E2:F13)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=TEXT(A2,"mmm")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

YIIPPPEEEE!

Thank you!