Parsing a number in a cell

Kevalson

New Member
Joined
Apr 16, 2002
Messages
22
Office Version
  1. 2013
Platform
  1. Windows
I have an Excel sheet where I have to keep track of overtime.
The report I receive in Excel has all the hours worked in the cell.
Example.

Column A heading (A1) reads "Regular Hours".
Column B heading (B1) reads "Overtime Hours".
Cell A2 under column A shows the hours as 48, and cell B2 under column B shows 0.
It is supposed to be 40 under A2 and 8 for B2.
Basically it is a bug in the program that exports the time.

So instead I would like to add a button in a new blank Excel sheet. (This I know how to do)
Then I can just copy and paste from the report into the new Excel sheet. (This I know how to do also)
Then I want to click the button that will take the 48 in A2, change it to 40, and put the remainder of 8 into B2.

Now if the regular hours is less than 40, then I want it to leave it alone.
Sometimes the hours are 36 in A2, so obviously there is no overtime anyway for B2.
Now there is about 75 rows of time, so it would need to go and do this for all 75 rows.

So, long story short:

Need it to change the 48 to 40, and put the remainder in the next cell which is B2 in this case.

I hope this makes sense to everyone.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
What I think I would do is use helper columns containing formulas, such as
=MIN(48,A1)
and
=MAX(A1-48,0)

where A1 contains the initial time of 49 hours or whatever.
There are other ways of writing this type of formula as well.

There are also various ways of either getting VBA to either do the whole calculation for you, OR getting VBA to write these formulas into the spreadsheet, do the calculation, copy the results into the required places, and then delete the formulas.
 
Upvote 0
something along the lines of

Dim lr As Long
Dim c As Range, rng
lr = Range("a" & Rows.Count).End(xlUp).Row
Set rng = Range("a2:a" & lr)
For Each c In rng
If c.Value > 40 Then
c.Offset(0, 1) = c.Value - 40
c.Value = 40
End If
Next c
 
Upvote 0
Hi, here is some another code based option you can try.

Code:
Sub SplitOutOvertime()
With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
  .Offset(, 1).Value = Evaluate("INDEX(IF(" & .Address & ">40," & .Address & "-40,0),0)")
  .Value = Evaluate("INDEX(IF(" & .Address & ">40,40," & .Address & "),0)")
End With
End Sub
 
Upvote 0
Thanks everyone.
Had some help from a coworker and this is what we came up with.

Dim overtime As Integer
Sub Macro1()
'
' Macro1 Macro
'

'
Range("A1").Select
While ActiveCell.Value <> ""
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
If ActiveCell.Value > 40 Then
overtime = ActiveCell.Value - 40
Cells(ActiveCell.Row, ActiveCell.Column + 1) = overtime
ActiveCell.Value = 40
End If
Wend

End Sub
 
Upvote 0
Hi, you might want to also try out the other suggestions - it's rarely necessary to select cells to work with them and it's often a slower approach.
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top