Dataman12-maz-qe.weebly.com



05q-Timesheets in ExcelName: FORMTEXT ?????This lesson will show you how to create a weekly timesheet you can easily adjust to track biweekly and monthly times and projects. Directions: READ the info. DO the actions listed as highlighted in YELLOW. Some of it may be confusing as this lesson is almost directly from the website. See if you can do it and make a working time sheet. p.s. As is, this sheet doesn't document breaks or allow for flexibility in scheduling. However, the basic structure and formulas are present, so you can easily customize the sheet to fit your needs.Note: This article and sample timesheet are from 1: Determine your needsWhen preparing a template other people will use, you need to consider several options:How to collect and validate input from users to eliminate errors and other invalid data.How users will access the template — from an Excel workbook on their local systems or via a browser and a Web-based application.How to protect the template sheet so users can't alter formulas and automated features.How to secure confidential data.If you're creating a timesheet for only yourself, your job is less complicated.2: Enter labelsThe first step to actually building the timesheet is to enter the appropriate labels. That includes the headings:All applicable employee information, such as name, social security number, employee identification number, department, and manager. A time period. This could be the first day of the work week, the start and end date of a bimonthly time period, or even the first day of a fiscal month.Time in and out, breaks, sick, vacation, overtime, and so on.Subtotals and grand totals, as required.Employee and approving manager signature lines, if required.A weekly timesheet for tracking hours might resemble the one shown in Figure A. (below)Open a new Excel document. Name it FORMTEXT 05q-Excel Timesheet-name and copy Figure A into it by typing in the info and make the columns wide enough to fit the headings. Copy all formatting, too, such as: the bold, merge and centring, center the column headings, and apply the appropriate borders. Put your name in the name section!In real life, you can customize the labels to fit your specific needs.752475630555Figure A 3: Automatic datesYou can require users to enter all their work dates themselves, but that leads to mistakes. If you know the exact time-period, the simplest solution is to automate the required dates as follows:Have users enter the first date of the time-period in cell B2. [So put today’s date in Cell B2].In the first cell in the Date column, A7,make Excel look for the input date using the formula85344030861000182880028575000=IF(B2<>"",B2,"")as shown in?Figure B.Figure B-152400149415500This will make the sheet generate dates based on the first date of each time period.If the time period cell (B2) is blank, this formula returns a ‘zero-length string’. If there's a value, the formula returns it. The formula will return a weird date value until we format it (which we'll do later).In cell A8, enter the formula =IF(A7<>"",A7+1,"")as shown in?Figure C.** If you get an error message like #VALUE! Then you have probably used bad grammar when entering the date in cell B2.** what shows up in the cell will NOT be a date … yet. Just wait.228600-37465002202180-45085Figure C:This formula adds 1 to the value (date) in cell A7.Copy the formula in cell A8 as needed. For instance, if you're tracking time by the week, copy the formula in cell A8 to cells A9:A13 for a total of seven rows (A7:A13). For a biweekly timesheet, you'd copy the formula to cell A20, and so on.So, make a biweekly timesheet … by copying the formula in cell A8 all the way down to cell A20. [just ‘flash-fill’].The next step is to enter a formula in column B that returns the name of the weekday for the dates in column A. To do so, enter the simple formula?=A7?in cell B7 and copy it to cells B8:B20. (Later, we'll format B7:B20 to display the day of the week by name rather than the actual dates shown in column A.) If there's no date in cell B7, the sheet will appear empty.So do that now. Enter =A7 in cell B7 and flash fill it down to cell B20.*** Again, … it won’t look right yet. That’s next.4: Format Date and Day of Week columnsRight now, the General format displays serial values in the Date and Day of Week columns. First, let's format the dates in column A, as follows:Select A7:A20.Right-click the selection and choose Format Cells from the context menu.On the Numbers tab, select Date from the Category list, choose the appropriate format, such as d/m/yy, from the Type list, and click OK.Next, format the dates in column B, as follows:Select B7:B20.Right-click the selection and choose Format Cells.On the Number tab, choose Custom from the Category list.Enter?dddd?in the Type field, as shown in?Figure D, and click OK.18288005651500Figure DThe dddd format displays a date value as its day of the week.As you can see in?Figure E, the sheet clearly denotes the dates for which you're tracking hours. Now you're ready to start adding formulas for tracking time values.Figure EThe sheet generates dates for each time period if you provide a beginning date (cell B7).5: Enter a formula that calculates the first eight hours of each day – subtracts overtime OUT … then calculate it later.At this point, we need a formula that evaluates the In and Out time values up to and including the first eight hours of each day. If overtime isn't an issue, you won't need such a complex formula. However, for most hourly employees, overtime is a possibility. Enter the following formula into cell G7, as shown in Figure F, and then copy it to cells G8:G20=IF(((D7-C7)+(F7-E7))*24>8,8,((D7-C7)+(F7-E7))*24)If the number of hours worked in one day is greater than eight, the formula returns 8. If the number or work hours is equal to or less than 8, the formula returns that amount. For now, the formula returns 0 because there are no time values to evaluate.Figure FThis formula returns the first eight hours of each work day.6: Enter the formula that calculates overtime for each dayOvertime is hours over eight in any given day or any hours over 40 for the week. This sheet only tracks overtime by the day. To track overtime by the day, enter the following formula into cell H7, as shown in?Figure G, and then copy it to cells H8:H20=IF(((D7-C7)+(F7-E7))*24>8,((D7-C7)+(F7-E7))*24-8,0)Figure G next page – notice that columns G and H have 2 decimal places. Go ahead and make 2 decimal places now.When the number of work hours is greater than 8, this formula returns the overage.7: Format In and Out columnsEntering In and Out values can muck things up a bit if they're not formatted correctly, but before we format those cells, let's take a look at why you even need to.Figure H?shows a few days' worth of In and Out values entered as general numbers. As you can see, the formulas just don't know how to handle them all.22555204318000Figure HWhen calculating time, work with valid time values to avoid troublesome errors.Now, you might think that a different set of formulas could handle general numbers. What would happen if you delete the *24 components in both formulas? Those formulas work up to a point, but neither handles all timelines. For instance, neither formula can handle an In value that's greater than its companion Out value (such as row 10 in Figure H).To format the In and Out values appropriately, do the following:Select C7:F20.Right-click the selection and choose Format Cells.On the Number tab, choose CUSTOM from the Category list.Select the h:mm format and click OK.This forces people to use the 24 hour clock. So type a RED, BOLD NOTE in font “Bodoni MT Black” to your users in cell C4 that says they “must use 24 hour clock to enter times”.8: Provide In and Out default valuesBecause entering time values is prone to errors, consider providing default time values. That way, users will have fewer opportunities to introduce errors into the sheet. The default values you save with the sheet depend on each user's schedule, and you might have to provide individual sheets for many individuals. However,?Figure I?shows the most common 40-hour week schedule.Figure IEnter default time values to avoid user input errors.The default values shown here are entered using a 24-hour clock. 13:00 is 1:00 PM (check the Formula bar) and 17:00 is 5:00 PM. It works to enter 1:00 and 5:00, but you must INSIST AND FORCE your users to also enter the AM/PM. Excel will assume that 1:00 is 1:00 AM and 5:00 is 5:00 AM if you don't specify that it's PM. Either way, entering time isn't intuitive for most users, so providing the default values is helpful.In this example sheet, Saturday and Sunday aren't regular workdays, so it stores 0 values. Of course, you can customize the days normally worked to suit each employee.Right now, enter the SAME times in your EXCEL sheet to match the ones in Figure I. [Your workweek probably won’t start on Monday in your Excel sheet, but that’s fine for now. Just adjust your times]. Then add 7 more days of time. Make some of the times go past noon and some late workdays and some early. Make some days have overtime. Change it up. You can give yourself 2 days off out of the 5 if you want … or make yourself work 7 days. Whatever.9: Enter defaults for sick and vacation timeYou'll want to enter default values for sick and vacation time. For now, just enter 0s in all of the Sick and Vacation cells. Then format as General displaying two decimal places, as shown inFigure J. Format these values as follows:Select I7:J20.Right-click the selection and choose Format Cells.On the Number tab, choose Number from the Category list and click OK.By default, the Number format assumes two decimal places, and some employers set that to 0. The two decimal places will allow employees to specify partial hours such as 3.25 hours means 3 hours and 15 min. Most companies allow for this … but will also reduce pay in 15 min increments if you are even 1 minute late. Figure JEnter default values for sick and vacation time.10: Enter a formula that calculates daily totalsThe next step is to total the daily hours in column K. To do so, enter the following SUM() function in cell K7 and copy it to cells K8:K20, as shown in Figure K:=SUM(G7:J7)Or just use autosum! Figure KThis simple SUM() function totals daily hours.11: Enter formulas that calculate weekly totalsYou'll need to calculate weekly totals for each category of time. To do so, autosum from G7:G20 … or type the following function in cell G21 and copy it to cells H21:J21, as shown in?Figure L:=SUM(G7:G20)Figure LSUM() functions total weekly hours.12: Validating sumsThe week's total hours, which you'll want to display in cell K21, should be the same whether you sum the daily totals in column K or the hourly components in row 21. By checking both ways, you add a level of validation to the sheet. Should the totals not match, you want a formula that alerts you to the problem. Enter the following formula in cell K21, as shown in?Figure M:=IF(SUM(G21:J21)=SUM(K7:K20),SUM(G21:J21),"Error!")If you end up with more than 2 decimal places … reduce it to only 2 decimal places.Put a “Thick Box Border” around Cell K21.Figure MVerify subtotals using this IF() function.13: Fine-tune the formattingThe sheet is now functional, but you'll want to add a few borders to distinguish the sections and totals. Just select each section and choose the appropriate border from the Borders drop-down palette. You might also want to add shading to the weekly total cells in row 14.?Figure N?shows the sheet now.Figure NAdd borders to separate sections.14: Ensure valid input valuesUsers will have to change some data, but the sheet is specific in what types of values it needs. To protect the sheet's purpose, you can restrict users to specific types of data. For instance, cell B2 must be a valid date for the date- and day-generating formulas in columns A and B to work. Enable data validation for this cell as follows:Select cell B2.From the Data menu, choose Data Validation.On the Settings tab, choose Date from the Allow drop-down list.Choose Greater Than from the Data list.Enter?Jan 1, 2000 or whatever year you would have started that job…?in the Start Date field, as shown in?Figure O, and click OK.Figure OMake sure users enter only valid values.When validating this particular date, you can narrow things down a bit. However, if you just want to ensure that the input value is a valid date, the above works fine.You can also ensure that the In and Out entries are valid time values, as follows:Select C7:F20.Choose Data Validation from the Data tab.On the Settings tab, and choose Time from the Allow list.In the Start Time field, enter?0:00. In the End Time field, enter?23:59?and click OK.As with the date value, you can narrow down the acceptable times. You can even limit the columns individually. However, the "between 0:00 and 23:59" settings will reject anything but a valid time entry.To protect the Sick and Vacation columns, set up validation as follows:Select I7:J20.Choose Data Validation from the Data Tab.On the Settings tab, choose Decimal from the Allow list.Enter?0?and?8?as the Minimum and Maximum values and click OK.Choosing Decimal in step 3 will allow users to enter partial hours. Choose Whole Number from the Allow list to restrict users to only hours. In addition, you can enter clues to the type of data the user must enter, as well as error messages. Applying validation rules adds the first layer of protection.15: Protect formulasOnce the sheet is finished, add a CRITICAL layer of protection by specifying what cells users can alter before you distribute it. You can do so as follows:Select cells A1:K21 then right-click and get into “Format Cells”Select “Protection” and select “Locked”.Now [to unlock specific parts only] … Select B1:B4, then hold [ctrl] key and select C7:F20 then I7:J20. (Hold down the [Ctrl] key while highlighting each range.)Again get into “Format Cells”, Protection, and now UNSELECT “Locked”.Now go to REVIEW Tab and click “Protect Sheet”.DO NOT enter a password, but notice that you can make a password.UNCHECK the box for “Select Locked cells” CHECK the box for “Select Unlocked Cells.Click OKIf you were going to issue this to an actual person … or use it for yourself … you would delete any test numbers, such as the date in B2 and all your fake time data. But, leave everything.Now users can select only the cells they might need to update. Train your users to open the template and save a new file to create a new timesheet. That way, they can open the template with all the defaults to start each new time period.DONE!! COOL!!Save again … then submit your excel workbook to Mrs Maz via: password: QE-mrsmazDo NOT submit this set of instructions. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download