Totext and year

Mark Wilson
Mark Wilson used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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


demo

hope that helps
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
FWIW, I don't think either of those posts really addresses your situation.  I'm guessing that you have column headings for different months, which really have nothing to do with the current month.  mlmcc's suggestion subtracts 1 year from today's date, so it would only give you last January in January.  I suppose you could replace the first part of your iif with that, but that doesn't address the second part of your iif (when the current month is not Jan).

 Is there a better way to do what you're doing?  There are different ways.  What's "better" may depend on exactly what you're trying to do and why.

 As for only showing the last 2 digits of the year, the simplest thing is probably just to use the Right function:

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


 James
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Commented:
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

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial