Can I start my project codes from zero for the 2025 year?
I made a project intake sheet for tracking the amount of project request that my department gets. My department receives request for many projects, from different units around our organization hence why they have different pre-fix abbreviations in the screenshot below. Each project from their respective department gets a number, for example, if there are 5 projects they will not be counted conventionally (1, 2, 3, 4, 5). If 2 are from the accounting department, and 3 are from the IT department, the projects will be counted like so…
(We started from 100 but with this being the first IT project, instead of 1 or 01, project 1 is read as 101)
IT-2025-101, IT-2025-102, IT-2025-103
ACC-2025-101, ACC-2025-102
For 2024, we purposely started the first project at 100, but for 2025 we want to keep the same sheet, and start each project from there respective departments at 01. Is there any way to do that without starting a whole new sheet? Our projects continue to count from the previous year but we want a whole new count for the new year, without messing up 2024's count. For example,
If accounting had 3 projects in 2024 and 1 in 2025, the fourth project shouldn't be ACC-2025-104 (04 but, we started from 100). We would now want the fourth project starting in 2025 to read ACC-2025-01 since it's a new year.
Best Answer
-
Try this (assuming [UniqueID] is an auto-number column with no formatting):
=LEFT(Department@row, 3) + "-" + YEAR([Project Intake Date]@row) + "-" + IF(YEAR([Project Intake Date]@row = 2024, 100 + COUNTIFS(UniqueID:UniqueID, @cell <= UniqueID@row, Department:Department, @cell = Department@row, [Project Intake Date]:[Project Intake Date], IFERROR(YEAR(@cell), 0) = 2024), RIGHT("000" + COUNTIFS(uniqueID:UniqueID, @cell <= UniqueID@row, Department:Department, @cell = Department@row, [Project Intake Date]:[Project Intake Date], IFERROR(YEAR(@cell), 0) = IFERROR(YEAR([Project Intake Date]@row), -1)), 3))
Answers
-
@How are you currently creating the identifier?
-
Im creating the identifiers using this formula,
=LEFT(Department@row, 3) + "-" + (YEAR([Project Intake Date]@row) + "-" + IF(Prefix@row <> "", Prefix@row, "") + [Suffix Helper]@row + ""
-
Ok. And how are you populating the [Prefix] and [Suffix Helper] columns?
-
To populate the suffix helper we used this formula,
=IF(YEAR([Project Intake Date]@row) >= 2025, IF(LEN([Suffix]@row) = 1, "00" + [Suffix]@row, IF(LEN([Suffix]@row) = 2, "0" + [Suffix]@row, IF(LEN([Suffix]@row) = 3, [Suffix]@row, ""))), IF(YEAR([Project Intake Date]@row) < 2025, IF(LEN([Suffix]@row) = 1, "10" + [Suffix]@row, IF(LEN([Suffix]@row) = 2, "1" + [Suffix]@row, IF(LEN([Suffix]@row) = 3, [Suffix]@row, ""))), ""))
This formula adds leading zeros to any project for 2025 and beyond, then it adds a leading 1 to any project before 2025.
The prefix column populates using this formula,
=PARENT() + IF([Parent ID]@row = "TOP", "", PARENT([Suffix Helper]@row) + ".")
This formula helps generates the naming system we have for our projects.
Do you think it would have to do with changing this formula to a count if formula?
-
And (hopefully) final question… How is the [Suffix] column being populated?
-
The suffix column is being populated with this formula here,
=MATCH(UniqueID@row, COLLECT(UniqueID:UniqueID, [Parent ID]:[Parent ID], [Parent ID]@row, Department:Department, Department@row), 0)
This formula makes numbers for each department and counts them this way.
-
Try this (assuming [UniqueID] is an auto-number column with no formatting):
=LEFT(Department@row, 3) + "-" + YEAR([Project Intake Date]@row) + "-" + IF(YEAR([Project Intake Date]@row = 2024, 100 + COUNTIFS(UniqueID:UniqueID, @cell <= UniqueID@row, Department:Department, @cell = Department@row, [Project Intake Date]:[Project Intake Date], IFERROR(YEAR(@cell), 0) = 2024), RIGHT("000" + COUNTIFS(uniqueID:UniqueID, @cell <= UniqueID@row, Department:Department, @cell = Department@row, [Project Intake Date]:[Project Intake Date], IFERROR(YEAR(@cell), 0) = IFERROR(YEAR([Project Intake Date]@row), -1)), 3))
-
The UniqueID is a text/number column not an auto-number, and I'm guessing why this came up as an invalid operation?
-
In that case, insert an auto-number column with no special formatting and replace each reference in my last formula with the appropriate reference to the auto-number column.
-
our Auto number column is named ### Copy. Do I replace that with every place you have UniqueID?
-
Do you have any formatting applied in the column properties such as zero filling, prefixes, or suffixes?
-
No pre-fixes or suffixes
-
In that case, yes. [Unique ID] references would be replaced with references to your auto-number column.
-
Can you give me the formula replacing the Unique ID with the Auto-Number column (### Copy)?
-
You would just replace each reference to UniqueID with the name of your Auto-number column. If there are spaces, numbers, and/or special characters in your auto-number column name, you will need to wrap it in [square brackets].
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives