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:
And this is the code:
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:
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