Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-16-2020, 07:54 PM
appplejack007 appplejack007 is offline How can I cap numbers in a column? (Workbook included) Windows 10 How can I cap numbers in a column? (Workbook included) Office 2016
Novice
How can I cap numbers in a column? (Workbook included)
 
Join Date: Mar 2020
Posts: 2
appplejack007 is on a distinguished road
Default 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
File Type: xlsx Workbook.xlsx (22.0 KB, 6 views)

Last edited by appplejack007; 03-17-2020 at 03:23 PM.
Reply With Quote
  #2  
Old 03-18-2020, 08:43 AM
BobBridges's Avatar
BobBridges BobBridges is offline How can I cap numbers in a column? (Workbook included) Windows 7 64bit How can I cap numbers in a column? (Workbook included) Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 644
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 03-19-2020, 02:13 AM
ArviLaanemets ArviLaanemets is offline How can I cap numbers in a column? (Workbook included) Windows 8 How can I cap numbers in a column? (Workbook included) Office 2016
Expert
 
Join Date: May 2017
Posts: 545
ArviLaanemets is a jewel in the roughArviLaanemets is a jewel in the roughArviLaanemets is a jewel in the rough
Default

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)
Reply With Quote
  #4  
Old 03-21-2020, 01:43 PM
appplejack007 appplejack007 is offline How can I cap numbers in a column? (Workbook included) Windows 10 How can I cap numbers in a column? (Workbook included) Office 2016
Novice
How can I cap numbers in a column? (Workbook included)
 
Join Date: Mar 2020
Posts: 2
appplejack007 is on a distinguished road
Default

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

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 1 01-12-2020 03:03 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
How can I cap numbers in a column? (Workbook included) Spliting column with text and numbers mjosic Excel 3 04-04-2012 05:56 AM


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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft