Need help to auto populate a formula to display a number
It is simply EYYWK##
-YY= year (e.g., 24 for 2024)
-WK=week (e.g. 15 for right now. The 15th week of the year)
-##=sequential e.g., 3x E-runs have been created this week, so the last "Full# in the book is E241503
Answers
-
You could use the YEAR and WEEKNUMBER functions to return those values, display only the last 2 characters from the year using RIGHT, and combine them in one string with an "E" at the start. Is this what you mean? Based on today that would be:
="E" + RIGHT(YEAR(TODAY()), 2) + WEEKNUMBER(TODAY())
Or if you have a date column you probably want to use that, otherwise your codes will change each week.
="E" + RIGHT(YEAR(Date@row), 2) + WEEKNUMBER(Date@row)
This will return 1-53 for week 1-53 rather than 01-53. Is that a problem? If so, you can add another section that will only appear if the week number is less than 10. This will put a 0 in the middle if the week number is less than 10.
="E" + RIGHT(YEAR(Date@row), 2) + IF(WEEKNUMBER(Date@row) < 10, 0) + WEEKNUMBER(Date@row)
The sequential bit at the end will depend on your data. How do you identify a run? A column with an ID or something in it, or do you count all rows with a date in that week number, or something else?
This formula will count how many rows have the same week number and assuming the rows are sorted in ascending date order, the first time a row has a particular week number, it will return 1, the second time 2, etc.
=COUNTIF(Date$1:Date@row, WEEKNUMBER(@cell) = WEEKNUMBER(Date@row))
You can add this to the end of the formula above.
="E" + RIGHT(YEAR(Date@row), 2) + IF(WEEKNUMBER(Date@row) < 10, 0) + WEEKNUMBER(Date@row) + COUNTIF(Date$1:Date@row, WEEKNUMBER(@cell) = WEEKNUMBER(Date@row))
If you want this to have a leading 0 for values less than 0 you can include another part and use an IF function to return the 0 as we did for week number.
="E" + RIGHT(YEAR(Date@row), 2) + IF(WEEKNUMBER(Date@row) < 10, 0)+ WEEKNUMBER(Date@row) + IF(COUNTIF(Date$1:Date@row, WEEKNUMBER(@cell) = WEEKNUMBER(Date@row)) < 10, 0) + COUNTIF(Date$1:Date@row, WEEKNUMBER(@cell) = WEEKNUMBER(Date@row))
I hope that is what you are looking for.
-
This formula gave me the first part correct- what I was looking for
="E" + RIGHT(YEAR([Created (Auto)]@row), 2) + IF(WEEKNUMBER([Created (Auto)]@row) < 10, 0) + WEEKNUMBER([Created (Auto)]@row)
I do have a date column. The column name is Created (Auto), so a created date will already populate when someone fills out the form to request an E-run number.
=E2415##
The sequential bit at the end will depend on your data. How do you identify a run? A column with an ID or something in it, or do you count all rows with a date in that week's number, or something else?-
Each person gets an ID (E2415##). In the E number, we would count how many requests in that week
E241501- 1st person to request
E241502-2nd person to request
E241503-3rd person to request and so on
after week 15 ends, we head into the 16th week
E241601-1st person to request
E241602- 2nd person to request and so on
-
Great! And it sounds like my guess was pretty good. The sequential part I suggested should work if you change the column name to the name you are using:
="E" + RIGHT(YEAR([Created (Auto)]@row), 2) + IF(WEEKNUMBER([Created (Auto)]@row) < 10, 0) + WEEKNUMBER([Created (Auto)]@row) + IF(COUNTIF([Created (Auto)]$1:[Created (Auto)]@row, WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row)) < 10, 0) + COUNTIF([Created (Auto)]$1:[Created (Auto)]@row, WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row))
The caveat is that new rows must be added to the bottom of the sheet, so the column Created (Auto) is sorted sequentially and never re-ordered. As the sequence is based on relative position, if you delete or re-order rows the sequence number will change.
If you want the sequence number to be issued as a row is added, and not to change, it would be better to add an autonumber column to your sheet. Let me know.
-
Please share - Yes, adding an auto number would work better
Can I add more to the mix- Can I have it pick what kind of Run type? E or EH in the beginning
Perfect, thank you so much!!
-
To replace "E" with the contents of the Run Type column you change the part in bold
="E" + RIGHT(YEAR([Created (Auto)]@row), 2) + IF(WEEKNUMBER([Created (Auto)]@row) < 10, 0) +...
to
=[Run type]@row + RIGHT(YEAR([Created (Auto)]@row), 2) + IF(WEEKNUMBER([Created (Auto)]@row) < 10, 0) +...
How does the run type impact the sequence? Should the sequence increase based on the number of previous rows with the same week number or the number of previous rows of the same run type with the same week number? In other words, can you have an E241401 and and EH241401 or would the second row created in the week be EH241402 (and there would be no EG241401).
-
THANK YOU
It counts by each run, no matter which run it is
How do you auto-populate this number when the row is added - you were saying auto-number earlier.
-
You can add an auto number column to your sheet (insert column - choose Auto number as the column type, see screenshot below).
This will iterate a new number as each row is added and the number will be retained by the row if it is moved. This will be used to create your sequence number. However, autonumbers run from a starting number and increase by 1 each time a row is added. You can add a prefix but it is a fixed value, it cannot be the formula we have that dynamically includes the year and week number.
So our next step is to take the auto number value and convert it into another number that starts at 01 for each weeknumber or each runtype and week number. I want to be sure I understand the requirement before creating that formula.
Can you confirm which of these is the way you want the sequence to work?
- EH240101
- EH240102
- E240103
- E240104
- EH240105
or
- EH240101
- EH240102
- E240101
- E240102
- EH240103
-
- Confirm this way
- EH240101
- EH240102
- E240103
- E240104
- EH240105
Thank you!
-
Please name your autonumber column Row ID and create a temporary column to try this out:
=COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))
This will count the number of rows where the created year and week number are the same as the current row but the row id is the same or less. The count will therefore increase with each row ID.
Check this gives you the sequence that you want. If so, you can delete the temporary column and use this formula to replace the original sequence number at the end of the formula we have been using so far.
Change
=[Run type]@row + RIGHT(YEAR([Created (Auto)]@row), 2) + IF(WEEKNUMBER([Created (Auto)]@row) < 10, 0) + WEEKNUMBER([Created (Auto)]@row) + IF(COUNTIF([Created (Auto)]$1:[Created (Auto)]@row, WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row)) < 10, 0) + COUNTIF([Created (Auto)]$1:[Created (Auto)]@row, WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row))
to
=[Run type]@row + RIGHT(YEAR([Created (Auto)]@row), 2) + IF(WEEKNUMBER([Created (Auto)]@row) < 10, 0) + WEEKNUMBER([Created (Auto)]@row) + COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))
If you want to include a leading 0 when this count is less than 10, add another IF function:
=[Run type]@row + RIGHT(YEAR([Created (Auto)]@row), 2) + IF(WEEKNUMBER([Created (Auto)]@row) < 10, 0) + WEEKNUMBER([Created (Auto)]@row) +IF(COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))<10,0)+ COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))
Let me know how you get on.
-
It worked- perfect!!
ANOTHER request: this should be it
E241501 turns into another number we need to record is
M + Run Rype + Week number + Sequence + 0
ME15010
-
If all you are doing is adding M to the start and 0 to the end, then you just need to add these parts in bold, just like how we added "E" originally.
="M" + [Run type]@row + RIGHT(YEAR([Created (Auto)]@row), 2) + IF(WEEKNUMBER([Created (Auto)]@row) < 10, 0) + WEEKNUMBER([Created (Auto)]@row) + IF(COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row)) < 10, 0) + COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row)) + "0"
If you want to exclude year then delete that part (shown in bold):
="M" + [Run type]@row + RIGHT(YEAR([Created (Auto)]@row), 2) + IF(WEEKNUMBER([Created (Auto)]@row) < 10, 0) + WEEKNUMBER([Created (Auto)]@row) + IF(COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row)) < 10, 0) + COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row)) + "0"
-
Hi! Me again- they explained it to me wrong.
If it is EH, then it has its sequence in tenths
EH241610
EH241620
EH241630
EH+ week number+ sequence 1 with added zero end
MEH is WH+week number+ 1 with added zero at end
MEH1610
MEH1620
MEH1630
-
This is the E and EH formula, each new line is a block of text
E/EH is
=[Run type]@row
Year is
+ RIGHT(YEAR([Created (Auto)]@row), 2)
Week number is
+ IF(WEEKNUMBER([Created (Auto)]@row) < 10, 0) + WEEKNUMBER([Created (Auto)]@row)
Sequence number is
+IF(COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))<10,0)+ COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))
This is the M formula
M is
="M"
E/EH is
+ [Run type]@row
Week number is
+ IF(WEEKNUMBER([Created (Auto)]@row) < 10, 0) + WEEKNUMBER([Created (Auto)]@row)
Sequence number is
+ IF(COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row)) < 10, 0) + COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))
The 0 at the end is
+ "0"
You can take any of those chunks, replace the + at the start with an = and see what they do in isolation. This makes it easier to see what is going on.
If you look at the M formula sequence number you can see it is in 2 parts (separated by the +) The first par is the IF to add the leading zero if the sequence number is less than 10, this is in bold. The second part is the sequence number. So, if you delete the part in bold I think you will have the formula that you need.
+ IF(COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row)) < 10, 0) + COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))
You can do the same for the E/EH version.
-
So, how do I tell it
If it is EH it's in tenths when it is E it 01
-
If you need to do different things for E and EH you can use another IF function. Just like how we used IF to add the leading zero when the count was less than 10.
So if E should have a leading 0 and EH should not, you can put an IF around the part of the formula that returns the leading 0.
....+IF([Run type]@row ="E", ---part of formula for the leading zero---)+....
This will then only be added if the run type is E.
The part in bold is the part that adds the leading zero.
+IF(COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))<10,0)+ COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))
If EH has a static 0 at the end and E does not, you can do something similar by adding an IF at the end
+IF([Run type]@row ="EH", "0")
Help Article Resources
Categories
Check out the Formula Handbook template!
