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

  • Paul Newcome
    Paul Newcome Community Champion
    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))

«1

Answers