Link to home
Start Free TrialLog in
Avatar of Mark Wilson
Mark Wilson

asked on

Totext and year

I am using the following formuas for headings in my report

iif(month(today)=1,"Jan - " & totext((year(currentdate)-1),0,""),"Jan - " & totext((year(currentdate)),0,""))

i.e. the example is the jan heading, if the month of the current date is 1 then heading should be Jan + last year i.e. Jan-2011 else month plus this year i.e. Jan-2012

Is there a better way of doing this and is there a way of getting the year to 2 digts so it will diplay as Jan-11 or Jan-12?

Thanks
Avatar of Ess Kay
Ess Kay
Flag of United States of America image

you can set it as current date

 then right click it in the report

select format

and select the option MAR-99

as seen in the picture


User generated image

hope that helps
Avatar of Mike McCracken
Mike McCracken

You could do something like

To get Jan last year
CStr(DateAdd('yyyy',-1,CurrentDate),'MMM-yy')

Convert a date to MON-YY

CStr(CurrentDate,'MMM-yy')

ToText(CurrentDate,'MMM-yy')

mlmcc
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i think my way is simpler, and may be quicker, since there is no calculation going on

its simply a formatting function.


what it does is just reformat the entire field
if you need to change year, you can do that in the formula itself, and it will still be quicker than the other option

the solution below, has 3 calculations,
1> if statement  -assess the field, and decides which option to do
2> pull the year from the equation
3> convert it to text




also, it only handles January, not EVERY Month

iif(month(today)=1,"Jan - " & Right (totext((year(currentdate)-1),0,""), 2),"Jan - " & Right (totext((year(currentdate)),0,""), 2))

Open in new window

esskayb2d,

 The problem (as I see it) with your suggestion is that you're only producing the current month.  If the current month is January, 2012, you get Jan-12 (which is not what the OP wants), or Jan-11 (which is correct) if you use a formula to subtract 1 from the year.  But if the current month is May, 2012, for example, you'd get May-12, and that's not what the OP wants.  If the month is not Jan, they want "Jan-YY", with the current year in "YY".

 My guess is that this is something like a financial report, with a separate column for each month.  So, this column will always be for January, regardless of the current month.  They're just changing the year.  If the current month is Jan, the heading shows Jan of last year.  Otherwise, it shows Jan of this year.

 Like you said, it only handles January, but I think that's the point.  I'm guessing there are similar formulas for the other months, each in a separate column.

 James
so, a new report file for each month of the year?


makes no sence

one report can handle all months using different parameters


make a parameter for marchs, and the formatting formula will change the data to the correct format. you dont need to use 'current date' in the formula


the accepted way is regardless of the month in question, it will always be changed to 'Jan'
No, not a new report for each month.  Just "fixed" columns (eg. for the fiscal year).  The report is not run for a single month.  It has columns for all of the months in the year, or all of the months from the beginning of the year up to today, or something like that.  One place where you might use something like that formula would be if the report was for the previous 12 months.  Run the report today, and one month ago is Apr-12, two months ago is Mar-12, etc.  12 months ago would be last May, so that would be May-11, instead of May-12.

 Of course this is all just guesswork on my part, based on what the OP was doing in that formula, and reports I've seen that had columns similar to that.

 James