Results 1 to 4 of 4

Thread: Creating an invoice number based on todays date IN EXCEL

  1. #1
    Neophyte dawood_ee1's Avatar
    Join Date
    Jan 2020
    Posts
    3
    Articles
    0
    Excel Version
    2016

    Creating an invoice number based on todays date IN EXCEL



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am trying to figure out the formula for invoice creation based on todays date with numeric values , for ex. today date 24/1/2020 so my invoice number has to be like 241201,second invoice number to be 241202 incase the the third invoice on next day means it shall be 251203. anyone who can generate formula in excel.

  2. #2
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    39
    Articles
    0
    Excel Version
    2016
    =TEXT(TODAY(),"dmyy")&ROW(A1) copy down

  3. #3
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    900
    Articles
    0
    Excel Version
    Excel 2013
    You should set an example for your Workbook.

    Try
    Copy this VBA codes below to your Workbook and 'Save As' *.xls or *.xlsm file
    In ThisWorkbook module
    Code:
    Private Sub Workbook_Open()
    Call SetTodayDate
      Application.CutCopyMode = True 'clear clipboard (prevents asking when wb is closed)
    End Sub
    In standard Module1
    Code:
    Sub InvoiceNumber()
    'Optimize Macro Speed - Disable
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
        Range("B2").Select
        ActiveCell.FormulaR1C1 = "=R[-1]C[9]+1"
        Range("K1").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("B2").Select
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("K1").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=R[1]C[-9]"
        Range("B2").Select
    
    'Optimize Macro Speed - Enable
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.CutCopyMode = True 'clear clipboard (prevents asking when wb is closed)
    End Sub
    In standard Module2
    Code:
    Sub SetTodayDate()
        Range("A2").Select
        ActiveCell.FormulaR1C1 = "=TODAY()"
        Range("A2").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("K1").Select
        ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""dmyy"")&ROW(RC[-10])"
        'Range("K2").Select
        Range("B2").Select
        ActiveCell.FormulaR1C1 = "=R[-1]C[9]*1"
        Range("B3").Select
        Application.CutCopyMode = True 'clear clipboard (prevents asking when wb is closed)
    End Sub
    You can put the last two VBA codes into one Module

    When you open Workbook Excel it automatically sets the TODAY Date in the A2 cell to Sheet1.
    Clicking on the VBA button automatically increases the Invoice number.

    I believe that VBA experts can more elegantly solve the problem by shortening the code.
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    900
    Articles
    0
    Excel Version
    Excel 2013
    Did it help?
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

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