VBA round value to 2 decimals

Kra

Board Regular
Joined
Jul 4, 2022
Messages
160
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am trying to create workbook in which I will keep track of work hours for myself. I am new in VBA, so my code is messy (sorry for that). In cell F1 I want to see how many over hours / under hours I have in total. I managed to do it, but I cannot set it to display value with only 2 decimals after comma. How can I do it?

This is how sheet looks like:
1669119031614.png


And this is the code:

VBA Code:
Sub RectangleRoundedCorners2_Click()
        
 Dim wknr As Integer
 Dim wkd As String
 Dim rng As Range
 Dim lr As Long
 Dim x As Long
 Dim ct As String
 Dim total As String
 Dim rng1 As Range
 Dim hours As String
 
 
 Application.ScreenUpdating = False
 
 
 Set rng = Cells(3, 3)
 Set rng1 = Cells(1, 6)
 lr = Cells(Rows.Count, 7).End(xlUp).Row
 
 
 
Range("G3").Select
    ActiveCell.FormulaR1C1 = _
        "=ROUND(IF((OR(RC[-3]="""",RC[-1]="""")),0,IF((RC[-1]<RC[-3]),((RC[-1]-RC[-3])*24)+24,(RC[-1]-RC[-3])*24)-RC[-2]/60),2)"
Range("G3").Copy
Range("G3").PasteSpecial xlPasteValues
    
Range("A3").Select
    ActiveCell.FormulaR1C1 = "=ISOWEEKNUM(RC[2])"
Range("B3").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[1],""DDDD"")"
    
Range("A3:B3").Copy
Range("A3:B3").PasteSpecial xlPasteValues

Range("C3:F3").Interior.Color = xlNone


total = Application.WorksheetFunction.Sum(Range("G3:G" & lr))
   
ct = (lr - 2) * 8

'THIS LINE CALCULATES OVER / UNDER HOURS
hours = ct - total


'AND HERE IS WHERE IT DISPLAYS RESULT IN F1
If total > ct Then
    
    rng1 = Abs(hours) & " overhour(s)"
    rng1.Font.ColorIndex = 10
        ElseIf ct = total Then
            rng1 = ""
                Else
                    rng1 = hours & " underhours"
                    rng1.Font.ColorIndex = 3
End If


Application.CutCopyMode = False
Application.ScreenUpdating = True



End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You could use something like:

VBA Code:
rng1 = Format$(Abs(hours), "0.00") & " overhour(s)"
 
Upvote 0
Solution

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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