Generating Month from Date for Fiscal Calendar

Christina4

New Member
Joined
Sep 26, 2017
Messages
3
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
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.
 
Upvote 0
For Fiscal Year end June 30, July is month 1.


Excel 2010
ABCDEF
15-Jul-171-Jul1Jul17
210-Jun-1812-June12Jun28
339
4410
5511
6612
771
882
993
10104
11115
12126
13
2a
Cell Formulas
RangeFormula
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")
 
Last edited:
Upvote 0
For Fiscal Year end June 30, July is month 1.

Excel 2010
ABCDEF
1-JulJul
12-JuneJun

<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!
 
Upvote 0

Forum statistics

Threads
1,226,693
Messages
6,192,465
Members
453,725
Latest member
cvsdatreas

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
Back
Top