Washington State English Language Learners Data entry shortcuts

1. Washington State English Language Learners Data entry shortcuts Paul McCold, Ph.D. Migrant & Bilingual Education, OSPI

2. Is there an easy way for districts compute the number of school days enrolled?

3. Computing Days Enrolled NETWORKDAYS = ( start_date, end_date, holidays) MAX = ( value1, value2) MIN = ( value1, value2)

4. Step 1: Create a spreadsheet that lists the first and last day of school and all of the school holidays, like this one here  Step 2: Save this spreadsheet and keep it open. Name it something like “EnrollDays.xls”

5. Step 3: Create a formula for total days to test the values you entered. Put the cursor in the first cell under Total Days and click on the function button. Select the Date & time category and scroll to the function NETWORKDAYS, then click “OK”

6. Step 4: Enter the values in each of the lines by putting the cursor in the box and then selecting the correct cell of the spreadsheet.

7. Step 4: For the holidays box, click and drag across all the holiday date you entered, then click “OK”.

8. If the value does not equal 180 days, correct the values entered for first, last and holiday dates. You do not need to account for snow days since they simply move back the last school day without changing the number of days. Step 4: If you entered the correct number of holidays and the correct first and last school days, the results should show the required 180 days with the formula showing in the formula bar.

9. Step 5: Open the spreadsheet that you need to compute the Days Enrolled keeping the other file (Days Enrolled) open.

10. Step 5: Put the curson in the first cell under DaysEnr and click the formula button to bring up the insert function dialogue box. Again, select NEWWORKDAYS and click “OK”.

11. Step 6: To enter the first school date, click the cell reference box on the right of the Start_date line.

12. Step 6: With this box open, select the “Days Enrolled.xls” spreadsheet using the windows tab bar on the bottom of the screen.

13. Step 6: Place the cursor in the Function Arguments dialogue box and then select the First Day of School in cell A2.

14. Step 6: Once the cell reference enters the dialogue box, click the cell reference box on the right of the entry line (or push the “Enter” key on the keyboard.

15. Place the cursor in the entry box for End_date.

16. Repeat this process to enter the last day of school into the End_date box • click the cell reference box on the right of the entry line.

17. Repeat this process to enter the last day of school into the End_date box • click the cell reference box on the right of the entry line.

18. With this box open, select the “Days Enrolled.xls” spreadsheet using the windows tab bar on the bottom of the screen and select the Last Day of School cell B2. Once the cell reference enters the dialogue box, click the cell reference box on the right of the entry line (or push the “Enter” key on the keyboard.

19. Put the cursor into the Holidays entry box, and you are ready for the last part of this step.

20. Repeat the process to enter the list of holiday dates into the Holidays box. Click the cell reference box on the right of the entry line.

21. With this box open, select the “Days Enrolled.xls” spreadsheet using the windows tab bar on the bottom of the screen. Then click and drag across all the holiday dates and click the “OK” box to complete the formula.

22. Again, the result of this formula, shown in the formula bar, should be “180” days. Now you are ready to adjust the formula to account for late enrollments and early withdrawals.

23. Computing Days Enrolled NETWORKDAYS = ( first_date, last_date, holidays) MAX = ( value1, value2) MIN = ( value1, value2) NETWORKDAYS = (MAX(EnrollDate, first_day_of_school), MIN (WithdrawDate, last_day_of_school, holidays) Now you need to modify the NETWORKDAYS function to include a MAX function and a MIN function.

24. In the formula bar, insert after the first parenthesis “max(“ and then select the cell for the EnrollDate followed by a comma as shown.

25. In the formula bar, insert after the first reference to Sheet1!$A$2 a right parenthesis before the comma, and insert after the comma, “MIN(“ and then select the cell for the WithdrawDate followed by a comma as shown. Then insert after the second reference to Sheet1!$B$2 another right parenthesis before the comma, as shown.

26. If your formula is correct, you should see the actual days enrolled in the cell. If not, review the formula until it correctly displays the days enrolled.

27. Copy the formula down the column to the end, using copy/paste or by selecting the cell with the formula in it and placing the cursor over the right lower corner of the cell until it turns into a plus sign and double clicking the lower right corner.

28. Once the DaysEnr column is filled in, you need to change the formula in the cells into values that do not need the other spreadsheet open.

29. You can replace the formulas with the values by selecting the entire column (click on the column letter on top “N”).

30. Click on the copy button or use the keyboard equivalent “Cntrl – C”

31. Select the paste icon and choose “Paste Values”

32. You then need to clear this action (clear memory) by pressing the escape key.

33. If you did this correctly, numbers will appear in the formula bar instead of the formula.

34. Washington State English Language Learners data entry shortcuts FOR MORE INFORMATION CONTACT: Paul McCold, Ph.D. Migrant & Bilingual Education, OSPI (360) 725-6147 Paul.McCold@k12.wa.us