+ Reply to Thread
Results 1 to 10 of 10

Generate Invoice Number Based on date

  1. #1
    Registered User
    Join Date
    10-24-2020
    Location
    Singapore
    MS-Off Ver
    Office Home & Student
    Posts
    4

    Generate Invoice Number Based on date

    Good Day All,

    I am just curious whether am I able to auto generate invoice number based on actual date.

    E.g. Today is 7 Apr 2021 and my Invoice number usually will be 202104070001(DDMMYYY0001)

    My invoice number will be at "C5"

    So I change it manually. My VBA already auto +1 when I save the invoice and is will save as the customer name "C9"

    Anyone able to guide me on this please.

    Thank You.

    Below are my code for my invoice
    Please Login or Register  to view this content.
    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer
    Last edited by 6StringJazzer; 04-07-2021 at 08:54 AM.

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Generate Invoice Number Based on date

    Try using the Format method...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-24-2020
    Location
    Singapore
    MS-Off Ver
    Office Home & Student
    Posts
    4

    Re: Generate Invoice Number Based on date

    Hi Dangelor,

    I tired using your code but it only appear 1.

    It doesnt appper 202104070001. There no date on it.

    Please advice me.

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Generate Invoice Number Based on date

    Here is another method :


    REPLACE this line of code :

    Please Login or Register  to view this content.


    With a CALL to this macro :

    Please Login or Register  to view this content.
    Delete the line of code mentioned above and insert this in its place :

    Please Login or Register  to view this content.

    NOTE: you will need to 'seed' cell C5 with the starting number like this : 20210407-001

    Then you will need to change it again for each day. The macro does not provide this for you.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Generate Invoice Number Based on date

    To automate C5:

    =YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")&TEXT(DAY(TODAY()),"00")&"-001"
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Generate Invoice Number Based on date

    Run this...
    Please Login or Register  to view this content.
    Does it display the correct number?

  7. #7
    Registered User
    Join Date
    10-24-2020
    Location
    Singapore
    MS-Off Ver
    Office Home & Student
    Posts
    4

    Re: Generate Invoice Number Based on date

    Hi AliGW,

    I didnt know it so easy just using that formula. Thank you for your time and teaching.

    However, the formula did not stay when i save it.
    Last edited by Jongnj87; 04-07-2021 at 12:04 PM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Generate Invoice Number Based on date

    It isn't. That's just a supplement to post #4.

    This will not work on its own for serial numbers because the number generated by TODAY() would change each day. It's only for cell C5 referenced by the VBA code.

  9. #9
    Registered User
    Join Date
    10-24-2020
    Location
    Singapore
    MS-Off Ver
    Office Home & Student
    Posts
    4

    Re: Generate Invoice Number Based on date

    Oic,

    Okie thank you!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Generate Invoice Number Based on date

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are still new here I will provide it for you this time: https://www.excelguru.ca/forums/show...-Based-on-date.)

+ 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] Auto Generate Next Numerical Value e.g. Invoice Number According to Latest Date and Time
    By BOBBY RUSSELL in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-20-2020, 05:42 PM
  2. Generate list of invoice dates based on start date and invoice frequency
    By yasmin89 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-14-2020, 02:31 PM
  3. Generate Invoice Number + Index Match data intry to invoice
    By Trish123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2020, 12:37 PM
  4. [SOLVED] Generating incremental invoice number based on date
    By excellon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-19-2020, 04:07 PM
  5. Replies: 4
    Last Post: 11-04-2012, 12:43 PM
  6. [SOLVED] How do I generate a new invoice number when creating new invoice?
    By KiddieWonderland in forum Excel General
    Replies: 1
    Last Post: 03-14-2006, 11:20 PM
  7. automaticaly generate invoice number?
    By ajkiwi88 in forum Excel General
    Replies: 0
    Last Post: 02-15-2005, 10:21 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