Autogenerate Invoice Number based on Year, Month and Invoice Status


I am building an app for employees to submit their time and that information is stored in a Google Sheet. Below is the structure of the table/columns.

Table name: Time. With the following columns:

  • Time ID (UNIQUEID())
  • Date
  • Hours
  • Project ID (PID)
  • Time Entry Status
  • Invoice Number
  • Invoice Status

I would like to write an expression to automatically generate invoice number at the time of submission based the on the following.

Invoice number should be in the following format: YYMM-ProjectID-X

  1. I can get YYMM with TEXT([Date], “YYMM”)
  2. I can concatenate Project ID with TEXT([Date], “YYMM”)&"-"&[Project ID]&"-"
  3. However the last piece (X) is where I need help. The idea is, ideally, I would have one invoice per calendar month for a particular project. However, if someone forgets to enter time and later adds time once the invoice has already been issued, then a new invoice number should be generated and the last digit should increment by 1. For example, I have 10 time entries for Feb 2021 for Project ID (PID) 111 and 10 entries for same month for PID 112. All the Feb entries for PID 111 should have Invoice number of 2102-111-1 and the all the entries for PID 112 should have invoice number of 2102-112-1. Let’s say both invoices were already issues for payment. Thereafter, someone enters time for Feb for PID 111, in which case, I want the invoice number that is generated at entry to change to 2102-111-2.

Any help would be great appreciated.

Thank you

1 Like