In order to unlock cells for employee editing, simply: You'll also want to make sure certain cells remain unlocked, so that your employees can freely add their data - typically, you'll need "Morning hours", "Lunch time", "Afternoon hours", and "Name" cells unlocked, and formula cells locked.īy default, all your Excel cells are locked, but you'll be able to manage them in the Format cells window. Once you've created your templates and defined all formulas correctly, you'll want to lock certain cells, in order to make sure your formulas remain functional and correct. If employees change data in any of the cells, these changes will be reflected in the Monthly total. Again, as employees enter their "Morning hours" and "Afternoon hours" in the right cells, the "Daily totals" and "Weekly totals" get calculated, and the "Monthly total" gets calculated as well.Insert =sum(G14+G30+N14+N30) into the "Monthly total" cell (marked as P4 in this timesheet example).You'll need to select the "Weekly total" cells (marked as G14, G30, N14, N30 in this Excel timesheet example) in order to calculate the "Monthly total".Here's how you calculate time in a monthly Excel timesheet template: Formula 3: Calculating monthly working hours Once you've had "Weekly totals" calculated, you'll use this data to calculate the "Monthly total". You can create multiple tables like this in one Excel sheet, and then calculate "Daily totals" and "Weekly totals" for each. As soon as employees add hours in the "Morning hours" and "Afternoon hours" cells, this time automatically get added to their "Daily total", and this data automatically gets added to their "Weekly total".Use the =sum() formula again in the "Weekly total" cell with the data in the "Daily total" column, by selecting the G8 cell, and then expanding the selection to G14 ("=sum(G8:G12)").Here's how you calculate time in a weekly Excel timesheet template: Formula 2: Calculating weekly working hours To use the same "=sum()" formula on the remaining days, expand the selection in your table to cover the remaining cells.Įxpanding the selection to calculate the remaining days will be important when you want to calculate "Weekly totals".As soon as employees type the hours in the "Morning hours" and "Afternoon hours" cells, this time automatically gets added to the "Daily total".Insert =sum(D8+F8) into the "Daily total" cell (marked as G8 in this timesheet example). Here's how you calculate time in a daily Excel timesheet template: Formula 1: Calculating daily working hours In the next step, we'll see how to add up "Morning hours" and "Afternoon hours" to get the "Daily total" automatically. Now that you've added the basic labels, you'll need to add timesheet labels to your template.Īdd the dates, and divide the columns in order to clearly organize the most important data:įor clarity, we'll mark these times in the timesheet as "Morning hours" "Lunch time", and "Afternoon hours". You can add more information if you want or need to, but a simple approach is always best.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |