Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

Write a excel code to calculate monthly salary payroll

  1. User

    Write a excel code to calculate monthly salary payroll

  2. Assistant

    Sure! Here's an example code in VBA that calculates the monthly salary payroll based on employee details and their hourly rates:

    Sub CalculatePayroll()
        Dim employeeSheet As Worksheet
        Dim payrollSheet As Worksheet
        
        Set employeeSheet = ThisWorkbook.Sheets("EmployeeDetails") ' Assuming the employee details are in a sheet named "EmployeeDetails"
        Set payrollSheet = ThisWorkbook.Sheets("Payroll") ' Assuming the payroll details will be placed in a sheet named "Payroll"
        
        Dim lastRow As Long
        lastRow = employeeSheet.Cells(Rows.Count, 1).End(xlUp).Row ' Assuming the employee details are organized in column A
        
        Dim empID As Integer
        Dim empName As String
        Dim hourlyRate As Double
        Dim monthlySalary As Double
        
        ' Clear the previous payroll data
        payrollSheet.Range("A2:B" & payrollSheet.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
        
        ' Loop through each employee in the employee sheet and calculate monthly salary
        For i = 2 To lastRow ' Assuming the employee details start from row 2
         
            empID = employeeSheet.Cells(i, 1).Value ' Assuming employee ID is in column A
            empName = employeeSheet.Cells(i, 2).Value ' Assuming employee name is in column B
            hourlyRate = employeeSheet.Cells(i, 3).Value ' Assuming hourly rate is in column C
            
            monthlySalary = hourlyRate * 40 * 4 ' Assuming 40 working hours per week and 4 weeks in a month
            
            ' Place the payroll data in the payroll sheet
            payrollSheet.Cells(i, 1).Value = empID
            payrollSheet.Cells(i, 2).Value = empName
            payrollSheet.Cells(i, 3).Value = monthlySalary
            
        Next i
        
        ' Format the payroll data
        payrollSheet.Columns("A:C").AutoFit
        
    End Sub
    

    Make sure to replace the sheet names ("EmployeeDetails" and "Payroll") with the actual names in your Excel file.