Forms, Excel and Formulas

Hi Charlot,

---

 

1. In Column E, I need Column D - Column C. When I try to add an array formula, the value does not extend to include all cells in the column. 

That could be

 

=(D3-C3)*24

 

 

2. In column H, I need a formula that will deduct 0.5 from the value of Column E if the value of Column G is yes. 

As

 

=E3-(G3="Yes")*0.5

 

 

3. Column I provides the week number based on Column B. Not sure if I need this for the following steps. 

 You may use

 

=WEEKNUM(B3)

 

4. In a separate sheet in the same workbook, I need to know how much special leave each member of staff has utilised and how much time remains. 

To pick-up unique names use in first cell for names

 

=IFERROR(LOOKUP(2,1/(COUNTIF($A$2:A2,Sheet1!$A$3:$A$11)=0),Sheet1!$A$3:$A$11),"")

and for hours used

 

 

=SUMIF(Sheet1!$A$3:$A$11,Sheet2!A3,Sheet1!$H$3:$H$11)

 

 

5. In a separate sheet in the same workbook, I need to be able to see who has applied for Special Leave by date and by week. (Hence the need for the week number?)

 

Could be done with Pivot Table, to correctly show the time the workaround to repeat Start/End columns in General format and add these columns to Pivot Table. Filter weeks by slicer. Rest is formatting. Please see attached.

 

 

6. Finally, in a separate sheet in the same workbook, I would like to be able to select a name to see the special leave of just that particular person. 

 

Perhaps similar tp previous one.

 

 

---

Attached.