Complete user manual:
| 1. | The timesheet is built in Microsoft Excel for PC. During development, it has as well been tested in Microsoft Excel for Mac. According to Excel’s internal version control, the workbook is backward compatible with Microsoft Excel 2010 for PC. You must use Microsoft Excel to edit the worksheet (Google Sheets and other alternative editors will not suffice). |
| 2. | The workbook can be stored on a local device [computer/tablet/iPad/phone] or an intranet or cloud storage for electronic editing and supervision. |
| 3. | The sheets are set up with single page width [‘Page Setup’, ‘Fit to: 1 page(s) wide’]. If it is desired to print on paper, it can be accomplished by selecting ‘Scaling’ and ‘Fit to paper’ in the print setup. |
| 4. | The timesheet is designed for time management and does not include charts or economic calculations. If such derivations are needed, they can be included in the sheet entitled ‘Appendix’. |
| 5. | The current year is input in the ‘Settings’ sheet. The supported range is 1900-9999. Leap years are automatically accounted for in the calendar. |
| 6. | The first day of the week is selected in the ‘Calendar’ sheet. For instance, the US normally uses Sunday and Europe uses Monday [ISO-8601]. |
| 7. | Week numbers are displayed above the numbered dates in the sheets for each month. Be aware that the week numbering convention depends on the choice of the first day of the week. |
| 8. | Days and dates desired to be distinguished in red within the sheets for each month can be selected in the ‘Calendar’ sheet. Those are called ‘red days’. |
| 9. | Holidays are selected in the ‘Calendar’ sheet. The corresponding holiday dates are background filled in light gray within the sheets for each month. |
| 10. | If a red day or a holiday is not listed in the ‘Calendar’ sheet, it can be manually specified by filling in its name and date at the bottom of the ‘Calendar’ sheet. |
| 11. | When selecting certain cells, an explanation appears describing its content and whether it is input by the user [I] or output/calculated by Excel [O]. For instance, by selecting the cell ‘Extra Time’ in the ‘Jan’ sheet, a definition of the term appears. Such explanations are included in the ‘Settings’ sheet, ‘Calendar’ sheet, ‘Projects’ sheet, and ‘Jan’ sheet. |
| 12. | Regular time is defined as scheduled or agreed-to worktime. Regular time is the first item that has to be logged each day. The number of regular hours is chosen from a drop-down menu. The listed menu values are specified in the ‘Settings’ sheet. |
| 13. | The working percentage, or Full-Time Equivalent percentage, is specified in the top section of the ‘Settings’ sheet. This is the ratio between the scheduled number of regular work hours during a certain period (such as a week or a month) and the number of full-time regular work hours during that same period. For a full-time position, the work percentage is 100 %, whereas for a 50 % position it is typically 50 %. If ‘Constant Work Percentage’ in ‘Settings’ is ON, the work percentage will be constant throughout the year and equals the percentage specified at the top section of the sheet. If ‘Constant Work Percentage’ is OFF, a separate work percentage is specified per month. |
| 14. | The number of regular work hours per full day is specified in the ‘Settings’ sheet. This is the daily number of regular work hours at 100 % work percentage. The actual (average) number of regular work hours per day is auto-computed. This is the number of regular work hours per full day multiplied by the work percentage. |
| 15. | If the number of regular hours is the same every workday, it will equal the number of work hours per full day, multiplied by the work percentage. Example: If the number of work hours per full-day is 8 hours, and the work percentage is 60 %, this constant number of regular work hours per day is: 8 Hrs * 60 % = 4.8 Hrs If however, the number of regular hours is not the same every workday, it should still average to the number of work hours per full day multiplied by the work percentage over a certain period. The worksheet does not test whether this is the case. It is up to the user to log the correct number of regular hours. Example: A weekly work percentage of 60 % may for instance be accomplished by working 100% on Monday-Wednesday and taking Thursdays and Fridays off, or by working 100 % on Mondays and 50 % on Tuesday-Friday. |
| 16. | Flexible time is defined as hours earned by working extra time and/or overtime. Flexible hours can be taken out by working less on other days [often called time off in lieu or compensatory time]. The number of accumulated flexible hours is shown at the bottom left corner of the sheet for each month. A negative number of flexible hours denotes owed hours and need to be worked up later. The ‘Settings’ sheet has a switch for applying a maximum and a minimum limit to the accumulated flexible time. The limits will not stop the flexible time from exceeding those limits but will cause the flexible time to be shown in red if the limits are exceeded. |
| 17. | Whenever a number of hours is entered in the timesheet, any number of decimal places may be used, and all decimals are included in the computations in the timesheet. However, in order to save space, hours in the timesheet are displayed with a maximum of one decimal place. |
| 18. | Overtime is logged in hours. In the ‘Settings’ sheet, there is a switch entitled ‘Overtime Constraint’. If the switch is ON, which is the default setting, overtime cannot be logged if there is a time deficit. That means that overtime logged on a particular day cannot exceed the time worked, minus regular hours, minus possible owed hours up to that day. If an invalid number of overtime hours is located, the error label ‘OTE’, which stands for ‘OVERTIME ERROR’, appears in red. If ‘Overtime Constraint’ is set to OFF, then the upper daily limit for overtime is simply time worked minus regular hours that day. In the ‘Settings’ sheet, there is a switch entitled ‘Flexible Time from Overtime’. When it is ON, overtime hours are multiplied by a specified factor and added to flexible time. |
| 19. | Vacation is logged in hours. The number of vacation days available for the year is specified in the ‘Settings’ sheet. The number of remaining vacation days is shown at the bottom left corner of the sheet for each month. Vacation is normally defined as unpaid leave. If the switch ‘Whole Vacation Days’ in ‘Settings’ is ON, vacation hours must be logged as whole days. If it is OFF, the maximum number of vacation hours that can be logged each day equals the number of regular hours minus the number of hours of sick leave and granted leave [see below]. [If ‘Work Schedule’ is OFF, the maximum number of vacation hours equals 24 minus sick leave and granted leave.] An error message appears if an invalid value is entered. |
| 20. | Sick leave is logged in hours and is defined as paid leave. The maximum number of hours of sick leave that can be logged daily equals the number of regular hours minus hours of vacation and granted leave. An error message appears if an invalid value is entered. |
| 21. | Granted leave is logged in hours and is defined as paid leave, such as work travel or paid time off. The maximum number of hours of granted leave that can be logged daily equals the number of regular hours minus hours of vacation and sick leave. An error message appears if an invalid value is entered. |
| 22. | Extra time is defined as work time which is neither regular time nor overtime. While regular hours and overtime hours are logged, extra time is not logged, but is auto-calculated as follows: Extra hours = Hours worked + Vacation + Sick leave + Granted leave – Regular time – Overtime The calculation is subject to two conditions: 1. The sum ‘Vacation + Sick leave + Granted leave’ cannot exceed ‘Regular time’. 2. If ‘Overtime’ > 0, ‘Extra hours’ cannot be negative, since overtime cannot be owed. In the ‘Settings’ sheet, there is a switch entitled ‘Flexible Time from Extra Time’. If it is ON, extra time is added to the flexible time and hence can be taken out by working less on other days. |
| 23. | When ‘Work Schedule’ in the ‘Settings’ sheet is ON, regular time, flexible time, overtime, sick leave, granted leave and extra time apply as defined above, which is the default setting. When ‘Work Schedule’ is OFF, regular time, flexible time, overtime, sick leave, granted leave and extra time do not apply, but vacation can still be logged. This setting may be relevant for self-employed personnel or in the absence of a predefined work schedule. |
| 24. | Up to three clock-in/clock-out time intervals can be logged daily. The input format is ‘hh:mm’. Examples: ’08:30′ or ’16:45′ The alternative formats ‘8:30 AM’ or ‘4:45 PM’ cannot be used. Note that while the input format has a colon, the display format ommits the colon in order to save space. Example: If the input clock-in time is: ’08:30′, it is displayed without a colon, as: ‘0830’. |
| 25. | If there are overtime error[s] in the spreadsheet, it will stop the possibility to clock in/out on dates later than the first of those error[s] until they are corrected. An error message is displayed, requesting to “check for error labels in earlier columns, including earlier months”. If there are incomplete clock in/out interval[s] at certain date[s] in the timesheet, the label ‘CLE’, which stands for ‘CLOCK ERROR’, appears in red at those dates. Further clock in/out times cannot be logged until those interval[s] are completed. Example: If the first clock-in time on January 17th is logged as 08:30, and no corresponding clock-out time is logged at that date, then if a clock-in time is attempted to be logged at a later date, an error message appears, requesting to check for incomplete earlier clock interval[s]. |
| 26. | A ‘Balancing Clock Out Time’ [BCT], which is the clock out time that results in zero remaining flexible time, is shown if it occurs within the next 24 hours. It is displayed in the last row. |
| 27. | When ‘Core Period’ in the ‘Settings’ sheet is ON, clock in/out times for non-holidays are underlined if they occur within the specified core period of the day. |
| 28. | New projects are registered in the ‘Projects’ sheet. Up to 40 projects can be defined. |
| 29. | Each project has a description [required], an allocated start date [optional] and an end date [optional]. A data bar shows the fraction passed of the allocated time period. The user may compare this percentage with information about the actual project completion, in order to decide whether the project is on track or not. |
| 30. | In the sheets for each month, there is a button labeled ‘PROJECTS’. When a new project is added to the ‘Projects’ sheet, this button must be pressed in order to update the list. For the button to work, macros must be activated upon opening the workbook in Excel. |
| 31. | The hours that are worked each day are distributed between the projects that worked on that day. For every single date, the timesheet checks whether the sum of all hours logged on the projects until that date differs from the total hours worked up to that date. Whenever a difference is found, the difference is displayed with the label “HOURS TO LOG”, which means those hours remain to be logged on the projects on that particular day. This computation is made in such a way that errors do not propagate. [*¹] |
| 32. | For each project, the total number of hours logged monthly and annually are shown near the right edge of the worksheet. |
| 33. | In the sheets for each month, the projects can be sorted on ID, start date, end date, percentage, or the number of monthly or annual hours, by pressing the respective triangle below the header cell. |
| 34. | Near the bottom of each sheet, there is a row for comments. When pressed, a drop-down menu appears. The entries [comments] in the menu are defined in the ‘Settings’ sheet. Comments may for instance be used to specify the reasons for absence, different types of overtime, etc. |
| 35. | In order to prevent unintended editing, the spreadsheet includes format and data validations. Cells that are not intended for editing are locked. |
| 36. | The footer of the worksheet for each month shows the full workbook filename, file path, tab name, and page number. The header shows the current date and time. |
| 37. | The worksheet has a button for deleting all input data from the sheets ‘Jan’-‘Dec’. It is located at the bottom of the ‘Settings’ sheet. The button can be used to prepare the worksheet for the new year. |
| 38. | For questions or feedback, please send an e-mail to: info@exceltimesheet.com. |
| [*¹] | During the time from January 1st until any given date in the timesheet, the total number of hours logged on the projects should equal the total number of hours worked on the projects. The difference between these two numbers is aimed at being kept within a threshold of +/-0.05 hours at any given date in the timesheet, by displaying a notification of the remaining number of hours to be logged each day. Example: If the user has worked 8.03 hours one day and is notified to log 8.1 hours [rather than 8.0 hours], this is due to compensation for the accumulated difference between the number of hours worked and the number of hours logged. If this accumulated difference was, say, 0.04 hours before that day, then the accumulated difference including that day [and before any hours are logged] is: 0.04 + 8.03 = 8.07 hours |
| In other words, 8.07 hours remain to be logged that day in order to maintain a balance between hours worked and hours logged up to and including that day. Since hours are displayed with one decimal place, the timesheet will ask for 8.1 hours to be logged. When those hours are logged, the updated accumulated difference will be: 8.07 – 8.1 = –0.3 hours Whenever the accumulated difference between hours worked and hours logged falls outside the error interval of [–0.05, 0.05] hours, the timesheet requests an additional +/–0.1 hours to be logged in order to prevent the accumulated difference from propagating outside this error interval. |
Go back to Frontpage