L
LWilson
I’m working with a worksheet using Web Excel or CPM (Everest). As such I’m
combining functions from excel and web excel. Web Excel recognizes dates in
the following formats:
2009.Nov, FY2010Total (This represents my fiscal year which ends on 6/30
each year), 2009.Nov.30, CY2010Total (normal calendar year), and of course
the date formats in Excel.
Because I’m working with the different formats, it’s necessary to use the
concatenate function and if statements, to give the date formats I need.
I have the following formula in a cell to give me the date in the format
2009.Nov.30:
=IF(LEFT(N$19,2)="FY",M$20,IF(LEFT(N$19,2)="CY",M$20,IF(VLOOKUP(RIGHT(N$19,3),$I$25:$J$41,2,FALSE)=MONTH($K$44),CONCATENATE(N$19,".",DAY($K$44)),EVTIM($D$79,CONCATENATE(EVTIM($C$17,N$19,1),".","01"),-1))))
M20 is the date of my prior FY end or 6/30/09. The vlookup is a table with
the months of the year with the corresponding number, i.e. Column 1 is the
month Jan columns 2 is 1, Feb 2, Etc, so that I get the correct month in the
correct column.
I have the ability with WebExcel to look at prior FY data. A lot of my
formulas are driven off the report date (using Now function). If I change my
WebExcel date, the corresponding month, for example Dec, then the date for
Dec 2008 becomes Dec 1, 2008. When I need it to be Dec 30, 2008.
Is this clear as mud? Any help would be appreciated!
combining functions from excel and web excel. Web Excel recognizes dates in
the following formats:
2009.Nov, FY2010Total (This represents my fiscal year which ends on 6/30
each year), 2009.Nov.30, CY2010Total (normal calendar year), and of course
the date formats in Excel.
Because I’m working with the different formats, it’s necessary to use the
concatenate function and if statements, to give the date formats I need.
I have the following formula in a cell to give me the date in the format
2009.Nov.30:
=IF(LEFT(N$19,2)="FY",M$20,IF(LEFT(N$19,2)="CY",M$20,IF(VLOOKUP(RIGHT(N$19,3),$I$25:$J$41,2,FALSE)=MONTH($K$44),CONCATENATE(N$19,".",DAY($K$44)),EVTIM($D$79,CONCATENATE(EVTIM($C$17,N$19,1),".","01"),-1))))
M20 is the date of my prior FY end or 6/30/09. The vlookup is a table with
the months of the year with the corresponding number, i.e. Column 1 is the
month Jan columns 2 is 1, Feb 2, Etc, so that I get the correct month in the
correct column.
I have the ability with WebExcel to look at prior FY data. A lot of my
formulas are driven off the report date (using Now function). If I change my
WebExcel date, the corresponding month, for example Dec, then the date for
Dec 2008 becomes Dec 1, 2008. When I need it to be Dec 30, 2008.
Is this clear as mud? Any help would be appreciated!