How can I set a limit for numbers before it goes to the next digit?



If you look at the workbook, Column B goes from 00.00.00 to 00.03.58 but I would like to have it 00.00.00 ... 00.03.39, then 00.04.00, 00.04.01, ... 00.04.39, 00.05.00 and so on.
By default as we all know that it goes like 00.00.00 ... 00.03.99 then 00.04.00 and so on.

How can I do this?

Thank you!!

Attached Files

Workbook.xlsx (22.0 KB, 10 views)

The only way I can think of to do that is to set up the numbers in helping columns elsewhere in the workbook, probably in the same worksheet somewhere to the right.

Like this: In X1 and Y1 put the values 0 and 0.

In Y2 put =IF(Y1=39,0,Y1+1). Once you copy this formula down (as far as you like), it causes each value in Y to increment up to 39 and then start over at 0.

In X2 put =IF(Y2=0,X1+1,X1). When you copy this formula down, it causes X to stay the same until the value in Y has cycled back around to 0.

Now back in column A put =X1&"."&Y1&".00".

I haven't tested this code, but I hope it gives you the idea.

Until "00.39.39" this formula will do. After this you have to add another calculation level (the logic remains same).

Thank you guys. I'm going to work on this and see how it goes.