excel timesheet template calculate hours

What this formula does is “for the entire 8.5 hours payment is 550, so we are dividing the total amount by 8.5 hours to find the per hour pay rate then multiplying it with regular working hours to get the value for the time they have worked for”. Okay, now we have “what the regular working hours are and what the overtime hours is” Using these two, we need to determine the total pay.įirst, apply the below formula to calculate “Regular Pay”. Now choose the formatting color that you wish to highlight the “Sat” & “Sun” dates.

Now we need to highlight the rows wherever the date’s day is “Sat & Sun”, so enter the below formula first. Select the table without headings, click “Conditional Formatting”, and choose “New Rule”.Ĭhoose “Use a formula to determine which cells to format”. Whenever the weekend comes, we need to highlight those dates, so we use “Conditional Formatting” for auto-highlight these days. Use the TEXT function to convert the date from column B to today’s name. After inserting the column, change the date format to “DDD”. Next to the “Date” column, insert one more column to arrive “Day” name. We need to mention the dates involved in the mentioned month, so enter dates from 01 st Jan to 31 st Jan first. Next, we need to create headings to track employee-wise details, so I have listed below the common data we need for an employee.

Next, we need to create a space to mention the month’s name for calculating the timesheet. You can download this Excel Timesheet Template here – Excel Timesheet Template

Excel timesheet template calculate hours