Dates
Posted by Zoe Reyes on January 24, 2002 8:23 AM
I'm pretty "green" in Excel as you are about to find out, and would appreciate any assistance in solving the following:
I'm working on a spredsheet that has fiscal years in a row as text as follows: "FY02", FY03", etc., and in another row, months as follows: "JUL HRS", "SEP HRS", and so on.
I need to be able to return the last day of the month for each month, for instance, FY02 - JULHRS => Jul-31-2002.
Any advice on how to go about it will be kindly appreciated.
Thanks.
Zoe
Posted by Aladin Akyurek on January 24, 2002 8:46 AM
=DATE(RIGHT(A2)+2000,VLOOKUP(LEFT(B2,3),{"Jan","1";"Feb","2";"Mar","3";"Apr","4";"May","5";"Jun","6";"Jul","7";"Aug","8";"Sep","9";"Oct","10";"Nov","11";"Dec","12"},2,0)+1,0)
where A2 houses strings like FY 02 and B2 strings like JUL HRS.
Custom format the cell of this formula as:
mmm-dd-yyyy
==============
Posted by Steve Hartman on January 24, 2002 9:01 AM
Assuming FY02 is in A1 and JUL HRS is in A3:
=DATE(RIGHT(A1,2)+2000,MONTH(DATEVALUE(LEFT(A3,3)&"-"&RIGHT(A1,2)))+1,0)
Adjust cell references as needed for the other months and years.
Posted by Dan Aragon on January 24, 2002 9:36 AM
This is what I would do. In a spot on your worksheet that you aren't using take two columns and enter in on the first column the 12 3-letter month codes, and on the column next to it, put in the month numbers 1-12 that correspond to the month. Example in Cell Y1, you enter "Jan"; Cell Z1 you put "1", Cell Y2 you put "Feb", Cell Z2 you put "2"...and so on until you have all 12 months and their corresponding month numbers listed in the range Y1:Z12.
Now, lets say that you have "FY02" in cell A1 and in B1 you have "Jul HRS". In cell C1, you can use the formula
=DATE(RIGHT(A1,2),VLOOKUP(B2,Z1:AA2,2,FALSE)+1,1)-1
Which gives the result July 31,2002. You will need to format C1 as needed. Does that help?
Posted by Dan Aragon on January 24, 2002 9:38 AM
Oops made a slight mistake in formula
Should be:
=DATE(RIGHT(A1,2),VLOOKUP(B2,Y1:Z12,2,FALSE)+1,1)-1
Sorry
Posted by Juan Pablo G. on January 24, 2002 9:50 AM
Just another option, without using VLOOKUP, but needs Analysis ToolPak.
Months in A2:A13, years in B1:F1. Formula in B2 and drag.
=EOMONTH(DATEVALUE("1/"&LEFT($A2,3)&"/"&(2000+RIGHT(B$1,2))),0)
Juan Pablo G.
Posted by IML on January 24, 2002 10:56 AM
One more, just for fun
assumin FY03 in a2 and DEC HRS in b2
=DATE(YEAR((SUBSTITUTE(B2," HRS"," 1, ")&SUBSTITUTE(A2,"FY ",20))+0),MONTH((SUBSTITUTE(B2," HRS"," 1, ")&SUBSTITUTE(A2,"FY ",20))+0)+1,0)
Note this requires your FY and HRS to be in caps to work.