Microsoft Office Forums How can I cap numbers in a column? (Workbook included)
 Register FAQ Search Today's Posts Mark Forums Read

#1
03-16-2020, 07:54 PM
 appplejack007 Windows 10 Office 2016 Novice Join Date: Mar 2020 Posts: 2
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!!
Attached Files
 Workbook.xlsx (22.0 KB, 6 views)

Last edited by appplejack007; 03-17-2020 at 03:23 PM.
#2
03-18-2020, 08:43 AM
 BobBridges Windows 7 64bit Office 2010 32bit Expert Join Date: May 2013 Location: USA Posts: 644

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
03-19-2020, 02:13 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 545

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
03-21-2020, 01:43 PM
 appplejack007 Windows 10 Office 2016 Novice Join Date: Mar 2020 Posts: 2

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

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post excelforsue Excel 1 01-12-2020 03:03 AM awpatts Excel 1 05-10-2017 06:56 AM nrcahill Excel 1 03-14-2016 12:26 PM officeboy09 Excel 5 11-05-2013 01:49 AM mjosic Excel 3 04-04-2012 05:56 AM

All times are GMT -7. The time now is 10:29 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top