Forum Discussion
Solved
Forms, Excel and Formulas
Dear Experts
I have a google form which I am trying to migrate to Excel and need some help with formulas and functions. I am not an expert but can handle the basics. However, I am not sure where to start with this and my attempts do not seem to be producing any decent results.
The context is a school in which employees can apply for specific amounts of time off during a day. Some of this time of comes out of x amount of hours of "special leave" whereas if it is medical, then it doesn't. There is also a 30 minute break which needs to be taken into account, so that if the time off bridges the 30 minute break, this is not deducted from said special leave. This is an example of the excel sheet (as created from a Form).
| A | B | C | D | E | F | G | H | I |
| Name & Surname | Date | Start Time | End Time | Hours | Reason for Special Leave | Includes Break | Actual Time | Week |
| John | 08/11/2017 | 08:15:00 | 14:15:00 | 6 | Personal | Yes | 5.5 | 45 |
| Mary | 09/11/2017 | 08:15:00 | 14:15:00 | 6 | Personal | Yes | 5.5 | 45 |
| Peter | 26/09/2017 | 08:15:00 | 10:00:00 | 1.75 | Medical | No | 0 | 39 |
| Agnes | 02/10/2017 | 08:15:00 | 12:00:00 | 3.75 | Personal | No | 3.75 | 40 |
| Paola | 22/09/2017 | 10:15:00 | 12:00:00 | 1.75 | Personal | No | 1.75 | 38 |
| John | 25/09/2017 | 08:15:00 | 09:15:00 | 1 | Personal | No | 1 | 39 |
| Peter | 20/09/2017 | 10:30:00 | 11:30:00 | 1 | Personal | No | 1 | 38 |
| Samuel | 25/09/2017 | 08:15:00 | 10:30:00 | 2.25 | Personal | No | 2.25 | 39 |
| Stan | 27/09/2017 | 11:45:00 | 14:00:00 | 2.25 | Medical | Yes | 0 | 39 |
I need several things:
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.
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.
3. Column I provides the week number based on Column B. Not sure if I need this for the following steps.
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.
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?)
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.
I have attached an example file.
I would really appreciate some help with this.
Thank you.
Charlot Cassar
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.
Utilizando o Excel da suíte de aplicativos do Office 365, em português, um policial precisa dividir a soma do intervalo de células de A3 até A6 pela média do intervalo de células de B3 até B6. Na célula que vai receber o resultado, deve utilizar a fórmula:
Ex¹)=SOMA(A3:A6)/MÉDIA(B3:B6)
Ex²)=SOMA(A3..A6)/MÉDIA(B3..B6)
JUSTIFICATIVA
é possível observar que o resultado é idêntico as figuras 1 e 3. Portanto, o Excel do pacote Office 365 interpreta as funções do exemplo 1 e do exemplo 2 são exatamente da mesma forma e ambas as operações dão o mesmo resultado.
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.
Dear Sergei
Thank you so much for your help and for explaining the workings. I appreciate that.
In the following:
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)
Can I add an if function so that the formula will only calculate leave that is marked as Personal only?
And can pivot tables be made to update automatically? Once the form goes live, staff members will be making requests through the form throughout the scholastic year and ideally, the tables just update themselves.
Thanks again for the support.
Charlot
Hi Charlot,
To sum only Personal leaves you may add one more criteria using SUMIFS
=SUMIFS(Sheet1!$H$3:$H$11,Sheet1!$A$3:$A$11,Sheet2!A3,Sheet1!$F$3:$F$11,"Personal")
You can't update Pivot Table each time new value is added, if only with VBA programming. However, creating the Pivot Table you may check the option Add to data model. When in ribbon click Queries & Connections in Data group, right click on WorkSheetConnection and in properties you may select to update Pivot Table on file opening and/or refresh every few minutes. In any case Refresh All button works.
Another bonus will be you don't need helper columns for the Start/End time in such case.
In general better to organize you data in tables and use structured references for formulas. When you don't depend on your source data range size.
I added some tables in attached.
