Open Office Calc Practical : Employee Data

Dataset (paste this into A1:E11)

Step-by-Step Tasks (Students follow these in Calc)

1. Ensure Dates are real Date values
   - Select column E → Format → Cells → Date → choose DD/MM/YYYY (or your locale).
   (If dates paste as text, use Data → Text to Columns or use DATEVALUE to convert.)
2. Calculate Experience (years & months)
   - In cell F1 write header Experience.
   - In F2 put (Calc / OpenOffice style uses semicolons; Excel uses commas):
   =DATEDIF(E2; TODAY(); "Y") & " yrs " & DATEDIF(E2; TODAY(); "YM") & " mos"
   Drag/copy down F2:F11.
   Excel version (comma separators): =DATEDIF(E2, TODAY(), "Y") & " yrs " & DATEDIF(E2, TODAY(), "YM") & " mos"
3. Most experienced (earliest Date) & Least experienced (latest Date) — apply Blue/Red row color
   Goal: color the entire row A:E — Blue for most experienced (earliest join date), Red for least experienced (latest join date).
   1. Create two cell styles (optional but cleaner): Press F11 → New Style from selection → name them MostBlue (set blue fill + white text) and LeastRed (set red fill + white text).
   2. Select range A2:E11 (the data rows).
   3. Format → Conditional → Condition 1 → set Formula is and enter:
      $E2 = MIN($E$2:$E$11)
      Then choose Apply Style = MostBlue (or Format… set blue background).
   4. Click Add (Condition 2) → Formula is:
      $E2 = MAX($E$2:$E$11)
      Apply Style = LeastRed.
   Notes: If there is a tie (two employees have same earliest/latest date), both rows will be colored. In Excel follow: Home → Conditional Formatting → New Rule → "Use a formula..." and use the same formulas but with commas.
4. Filter employees joined between 01/05/2019 and 31/08/2023
   Method A — Standard Filter (Calc):
   1. Select the data range A1:E11.
   2. Data → Filter → Standard Filter.
   3. Set: Field = Date_of_joining, Condition = >=, Value = 2019-05-01 (or type 2019-05-01);
   4. Click the + (Add) and set second row Field = Date_of_joining, Condition = <=, Value = 2023-08-31 ; Operator = AND → OK.
   Method B — Helper column (recommended to avoid locale issues):
   1. In G1 write header InRange.
   2. In G2 enter (Calc semicolons):
      =AND($E2 >= DATE(2019;5;1); $E2 <= DATE(2023;8;31))
      Drag down.
   3. Then Data → Filter → AutoFilter and choose TRUE in column G to show only those rows.

   Result (from the sample): Rohan Singh, Raj Malhotra, Kavita Shah, Neha Patel, Amit Verma, Sneha Joshi, Kriya Kapoor (7 records).

5. Sort Designation-wise & calculate salary subtotal per designation
   Recommended: Pivot Table (clean & chart-ready)
   1. Select A1:E11 (include headers).
   2. Data → Pivot Table → Create → Current Selection → OK.
   3. In the Pivot Table layout: drag Designation to Row Fields and Salary to Data Fields (Function = Sum).
   4. Finish: You will see each designation and Sum of Salary.

   Subtotals (from sample):
   Accountant = 38,000
   Clerk = 25,000 + 24,000 = 49,000
   Developer = 55,000 + 60,000 + 57,000 = 172,000
   HR Executive = 42,000 + 46,000 = 88,000
   Manager = 75,500 + 80,000 = 155,500
   Total = 502,500

   Alternate: Sort the main sheet by Designation (Data → Sort) then use Data → Subtotals (if available) to insert subtotal rows.
6. Draw a Pie Chart of Designation vs Total Salary
   
   1. Use the Pivot Table result (Designation & Sum of Salary). Select those two columns.
   2. Insert → Chart → Chart type = Pie → Next/Finish.
   3. Right-click chart → Insert Data Labels → choose value & percentage if you like.
   4. Resize & move chart to a suitable place (or copy chart to a new sheet and Export as image for WordPress upload).
7. Format header row & Freeze top row
   - Select row 1 → Format → Rows → Height (optional).
   - Format → Cells → Font → Bold; Background color (light grey); Alignment → Center (for headings).
   - View → Freeze Rows and Columns → Freeze First Row (so headers stay visible while scrolling).
8. Protect the sheet from unauthorized edits
   - Tools → Protect Sheet → set a password (remember it!). Choose what users may still do (select cells, format cells, etc.).
   - To encrypt the workbook file so it cannot be opened without password: File → Save As → check Save with password (enter password when prompted).
   Excel note: Review → Protect Sheet and File → Save As → Tools → General Options → Password to open (for encryption).

Submission checklist for students

• Upload the workbook file (.ods) with applied conditional formatting and chart.
• Include screenshots showing: colored rows (blue & red), filtered result, pivot/subtotal screen, pie chart, and proof of protected sheet.
• Alternatively, submit a PDF export containing the same screenshots and the .ods file as attachment.