+ Reply to Thread
Results 1 to 3 of 3

Formatting Cell with date but without zeros as placeholders

  1. #1
    Registered User
    Join Date
    03-06-2020
    Location
    Frankfurt, Germany
    MS-Off Ver
    MS Office 2019
    Posts
    2

    Formatting Cell with date but without zeros as placeholders

    Hey guys,

    this one has been grinding my gears for a while now.
    I'm a bloody amateur on Excel VBA. However, this one at least seems like it could be simple to solve.

    My main objective is formatting cells to an invoice number with letters and symbols.

    Example:

    Input = 6
    Output = R06-0320

    So it supposed to be RXX-MMYY (XX = invoice number)

    The VBA code I'm using is:

    Range("F6:F101").NumberFormat = "R00" & "-" & Format(Now, "MMYY")

    But when I type a number in a cell it uses the number zero in the date as a placeholder.
    So if today is MMYY = 0320 and I put 6 as a cell value, then it will show me "R00-0326" instead of "R06-0320"

    How do I tell Excel to leave the date's zeros as a fixed number and use the other zeros (in this case "R00") as placeholders?
    Sorry if this may sound confusing.

    I would appreciate any advice. This has been bugging me for over a week now and google can't help me on this one either.

    Thanks!
    Last edited by Lanzy; 03-07-2020 at 03:42 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Formatting Cell with date but without zeros as placeholders

    Range("F6:F101").NumberFormat = """R""00""-" & Format(Now, "MMYY") & """"
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    03-06-2020
    Location
    Frankfurt, Germany
    MS-Off Ver
    MS Office 2019
    Posts
    2

    Re: Formatting Cell with date but without zeros as placeholders

    Dear AlphaFrog,

    at first I thought the amount of quotation marks were some high form of trolling, but you have proven me wrong.

    Thank you so much! I really appreciate it.

    Have a nice weekend

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Duplicate List & Replace Placeholders
    By seethesun in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-24-2017, 02:50 PM
  2. Format Placeholders in Word
    By kiwicantfly in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-22-2014, 06:17 AM
  3. Sorting numbers with different placeholders.
    By whitekerj in forum Excel General
    Replies: 1
    Last Post: 02-08-2007, 05:08 PM
  4. How to get column placeholders to continue in CSV
    By Nibs in forum Excel General
    Replies: 3
    Last Post: 10-25-2006, 08:13 AM
  5. placeholders
    By jhahes in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-01-2006, 10:31 AM
  6. SUMPRODUCT with PLACEHOLDERS?
    By titushanke in forum Excel General
    Replies: 5
    Last Post: 03-06-2006, 09:20 AM
  7. Zero Placeholders in number cell
    By B. Parizo in forum Excel General
    Replies: 3
    Last Post: 09-15-2005, 09:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1