Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Using month in Concatenate (2000)

    Hi all,

    I want to use the month name in a formula with Concatenate. To clarify: The description in column shout be: "accrual january 2004". In Column a there is the date of the last day of the month. Since I add a line every month with the same description except for the month I want to do this by formula. A combination of Concatenate and month does not satisfy me because I want the name and not the number of the month.

    I am sure it is possible but have no idea how. Does anyone have an idea?

    Regards Forbaty <img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using month in Concatenate (2000)

    Concatenate with TEXT(A1,"mmmm yyyy") where A1 is the cell containing the date. Say that cell B1 contains "Accrual" and cell A1 contains a date, the formula might be
    <pre>=B1&" "&TEXT(A1,"mmmm yyyy")
    </pre>

    In the Dutch language version with Dutch system settings, this would be
    <pre>=B1&" "&TEKST(A1;"mmmm jjjj")
    </pre>


  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Using month in Concatenate (2000)

    Thanks Hans,

    Worked like a charm. Could give me some general information on how to work with the "&" in formulas. I think I can have much use for it.


    Regards Forbaty <img src=/S/flags/Netherlands.gif border=0 alt=Netherlands width=30 height=18>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using month in Concatenate (2000)

    & is the concatenate operator in formulas and also in Visual Basic for Applications (the programming language for macros in Word, Excel etc.).

    If A1 contains "Jan" and B1 contains "Peter", you can use either =A1&B1 or =CONCATENATE(A1,B1) to produce "JanPeter".

    Both also work with more than two arguments; for example if you want to insert a space between the names, you can use =A1&" "&B1 or =CONCATENATE(A1," ",B1) to produce "Jan Peter".

    The & operator and the CONCATENATE function use the underlying values of cells referenced, not the displayed, formatted values. If you include a date in the concatenation, you will see a number, for Excel stores dates as numbers internally.

    Example: cell A1 contains today's date (April 20, 2004). The formula ="Today is "&A1 will result in "Today is 38097", because 38097 is the number stored for April 20, 2004. To display the date correctly, you must include the format in the formula by using the TEXT function: ="Today is "&TEXT(A1,"dd-mm-yyyy")

    The same holds if you want to concatenate text and numbers.

  5. #5
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Using month in Concatenate (2000)

    Thanks.

    I tink I can use this very often.

    Regards Forbaty

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using month in Concatenate (2000)

    Yes, it's a good trick to know. I use it quite a lot to do customised letters using xl rather than eg Word. For example, completing sentences like, "The loan you took out on [date] is overdue by [amount overdue]" and so forth. In both thoses cases, the data referred to in the & formula needs to be formatted to look like a data and an amount of money, respectively.
    The formulas you compile can look frightening - here's one -
    ="The situation is this: on "&TEXT(VLOOKUP($U$2,arrears!$A$3:$N$502,4,FALSE)," d mmmm yyyy")&", you borrowed "&TEXT(VLOOKUP($U$2,data!$A$1:$V$499,17,FALSE) ,"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •