I am building integrated testing scripts in Excel and would like to use a special numbering system with three numbers separated by a dot/period/decimal. The format is 00.00.000 where the first number represents the day the script is run (day 1, 2, 3, etc.), the second number is the script section number, and the third number is the step number.
An example of what I would like this to look like is:
Day 1
01.01.001
01.01.002
01.02.001
01.02.002
01.02.003
Day 2
02.01.001
02.01.002
02.01.003
02.02.001
02.02.002
etc...
Currently my formulae are as follows:
None of the custom number formats that I have tried work. The current custom number format I am using is ##"."##"."###. The output is close but not quite right and the formulae depend on zeroes preceding the numbers that have fewer digits than maximum allowed. For example, the step number should be three digits in length so for step number one, 001 should display, not 1.
The step number formula works correctly (except the output formatting) until I increment the script section number and then the formatting is thrown way out of whack and everything below that row fails.
I have also tried using other formats like #0.#0.##0, ##\.##\.###, and 00.00.000 without any difference in the formatting of the output.
My current output looks like this:
<tbody>
</tbody>So, here are my questions:
Thank you for any assistance that you can provide.
Shaun
An example of what I would like this to look like is:
Day 1
01.01.001
01.01.002
01.02.001
01.02.002
01.02.003
Day 2
02.01.001
02.01.002
02.01.003
02.02.001
02.02.002
etc...
Currently my formulae are as follows:
- step number
- =LEFT(C1,2)&"."&MID(C1,4,2)&"."&MID(C1,7,3)+1
- script section number
- =LEFT(E4,2)&"."&MID(E4,4,2)+1&"."&MID(E4,7,3)
- day
- =LEFT(E4,2)+1&"."&MID(E4,4,2)&"."&MID(E4,7,3)
None of the custom number formats that I have tried work. The current custom number format I am using is ##"."##"."###. The output is close but not quite right and the formulae depend on zeroes preceding the numbers that have fewer digits than maximum allowed. For example, the step number should be three digits in length so for step number one, 001 should display, not 1.
The step number formula works correctly (except the output formatting) until I increment the script section number and then the formatting is thrown way out of whack and everything below that row fails.
I have also tried using other formats like #0.#0.##0, ##\.##\.###, and 00.00.000 without any difference in the formatting of the output.
My current output looks like this:
| Day 1 | Formula |
| 01.01.000 | starting value |
| 01.01.1 | =LEFT(A1,2)&"."&MID(A1,4,2)&"."&MID(A1,7,3)+1 |
| 01.01.2 | =LEFT(A2,2)&"."&MID(A2,4,2)&"."&MID(A2,7,3)+1 |
| 01.2.2 | =LEFT(A3,2)&"."&MID(A3,4,2)+1&"."&MID(A3,7,3) |
| #VALUE! | =LEFT(A4,2)&"."&MID(A4,4,2)&"."&MID(A4,7,3)+1 |
| etc… |
<tbody>
</tbody>
- What is the best way to format the numbers in this column so that preceding zeroes are always displayed (e.g. 01.01.001)?
- Is my current incrementing formula compatible with that custom number format or would a different formula work better?
- How can I reset the third number (step number) back to 001 when the second number (script section number) is incremented?
- Related: How can I reset the second number (script section number) back to 01 when the first number (day) is incremented?
Thank you for any assistance that you can provide.
Shaun
