• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

concatenate a date field part 2 Got a message "Bad Number format String"

Thanks for the solution to ID 27652324 but now I want to use those results to create a formula that will subtact one year from the '201010'  to  '200910'.  I used a formula called "last year"  "Year ({?Selected_Date}) - 1"  got 2009, great  then tried to build  formula "totext({@Last year}, "yyyy")&ToText ({?Selected_Date}, "MM")  to build the '200910' but I got Bad Number format".  I am trying to use the parameter input date (10/23/2010) keyed in by the user to
select and compare employment counts in 2010 verus the same period 2009.

Can you point me in the right direction, again?  Thank you in advance.
0
Renshi04
Asked:
Renshi04
  • 2
  • 2
1 Solution
 
mlmccCommented:
WHat do you mean by period?

WHat formula is giving the error?

mlmcc
0
 
James0628Commented:
The problem is that in totext({@Last year}, "yyyy"), the "yyyy" format is to output the year from a date/datetime, but your {@Last year} formula is just a number (the year from {?Selected_Date}).  It's the same as saying ToText (2009, "yyyy"), and since 2009 is just a number, and "yyyy" is a date/datetime format, you get the "bad number format" error.

 You could fix this by changing the second formula to:

ToText ({@Last year}, "#") & ToText ({?Selected_Date}, "MM")

 The "#" format string is just a shorthand way of saying "no thousands separator and no decimal places").

 Or, for a slightly more direct solution, you could forget the {@Last year} formula and just subtract 1 from the year in that formula:

ToText (Year ({?Selected_Date}) - 1, "#") & ToText ({?Selected_Date}, "MM")


 James
0
 
Renshi04Author Commented:
Thanks, the user ask the wrong question.
0
 
James0628Commented:
FWIW, I don't see how mlmcc's post is the solution to your question.  I think I did answer your question, but if the question was "wrong" somewhow, maybe you should just ask to have it deleted.

 James
0
 
mlmccCommented:
Agree.  James seems to have answered the question that was asked.  If you haven't tried the solution you should.

Since the answer selected is obbviously not the solution I have unaccepted the comment.

mlmcc
Zone Advisor
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now