# How can I increment a custom number format with multiple periods?

Status
Not open for further replies.

##### New Member
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:

• 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>
So, here are my questions:

1. What is the best way to format the numbers in this column so that preceding zeroes are always displayed (e.g. 01.01.001)?
2. Is my current incrementing formula compatible with that custom number format or would a different formula work better?
3. How can I reset the third number (step number) back to 001 when the second number (script section number) is incremented?
1. 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

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Status
Not open for further replies.

Replies
11
Views
2K
Replies
151
Views
2K
Replies
0
Views
235
Replies
1
Views
517
Replies
5
Views
936

1,195,654
Messages
6,010,935
Members
441,575
Latest member
JOHNNY18031

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back