#1
|
|||
|
|||
How can I cap numbers in a column? (Workbook included)
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!! Last edited by appplejack007; 03-17-2020 at 03:23 PM. |
#2
|
||||
|
||||
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. |
#3
|
|||
|
|||
Until "00.39.39" this formula will do. After this you have to add another calculation level (the logic remains same).
Code:
="00." & RIGHT("00"&MOD(INT((ROW()-1)/40),40),2) & "." & RIGHT("00"&MOD(ROW()-1,40),2) |
#4
|
|||
|
|||
Thank you guys. I'm going to work on this and see how it goes.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I copy a workbook to another workbook and keep the formatting within the new workbook | excelforsue | Excel | 3 | 09-15-2020 03:37 AM |
Best formula for matching column data from one workbook into another | awpatts | Excel | 1 | 05-10-2017 06:56 AM |
automatically copy FIRST COLUMN ONLy to all sheets in workbook | nrcahill | Excel | 1 | 03-14-2016 12:26 PM |
Copy Column setting to other spreadsheets in an excel workbook | officeboy09 | Excel | 5 | 11-05-2013 01:49 AM |
Spliting column with text and numbers | mjosic | Excel | 3 | 04-04-2012 05:56 AM |