My current project is a leave card where employees can request leave and their manager then authorise their request. On the 'Welcome' page of the leave card are three statements which give information about their leave. All use fairly simple formulas, but I quickly realised that I needed to add a number of IF statements to account for if no leave had been booked, etc. With two of the statements I think I've caught all of the potential errors, but with the final one there are two potential errors I'm struggling with getting the formulas for that I'd appreciate some help with.
On the Leave page employees enter the dates of the leave they want to request - the leave start date in column D, and the leave end date in column E (dates are inclusive). On the 'Welcome' page, there's a statement that tells the employee when their next booked leave is, for example: "Your next booked leave is 13/10/20 to 19/10/20 which is 6 days of Annual leave." On the 'Welcome' page I have a couple of hidden columns with 'helper' cells which grab the relevant values from the Leave page to use in the formulas.
The basic formula for the statement in question is:
...but with the additional IF statements to account for if there is no future booked, the formula I have now is:
This takes care of cases where there is no leave booked, there's no future leave booked (it gives the last booked leave), and of course the next booked leave. The two situations I'm stuck with though are:
The possible states/error situations I've identified are:
The Welcome page
The Leave page
Any assistance with this appreciated.
Thanks,
Bliss
On the Leave page employees enter the dates of the leave they want to request - the leave start date in column D, and the leave end date in column E (dates are inclusive). On the 'Welcome' page, there's a statement that tells the employee when their next booked leave is, for example: "Your next booked leave is 13/10/20 to 19/10/20 which is 6 days of Annual leave." On the 'Welcome' page I have a couple of hidden columns with 'helper' cells which grab the relevant values from the Leave page to use in the formulas.
The basic formula for the statement in question is:
=IF(U11>TODAY(),"Your next booked leave is "&TEXT(U11,"dd/mm/yy")&" to "&TEXT(U12,"dd/mm/yy")&" which is "&U13&" days of "&U14&" leave.","")...but with the additional IF statements to account for if there is no future booked, the formula I have now is:
=IF(ISBLANK(Leave!D32),"You've not booked any leave yet. Why not go ahead and book some time off?",IF(U11>TODAY(),"Your next booked leave is "&TEXT(U11,"dd/mm/yy")&" to "&TEXT(U12,"dd/mm/yy")&" which is "&U13&" days of "&U14&" leave.", IF(U6<TODAY(),"Your last booked leave was "&TEXT(U6,"dd/mm/yy")&" to "&TEXT(U7,"dd/mm/yy")&" which was "&U8&" days of "&U9&" leave.","")))This takes care of cases where there is no leave booked, there's no future leave booked (it gives the last booked leave), and of course the next booked leave. The two situations I'm stuck with though are:
- dealing with when today's date is the start date of a period of booked leave or within a period of booked leave. Currently if today's date is the start date of a period of booked leave or within a period of leave, if there is some future leave booked it gives the details of that, but if there's no future leave booked, if today's date falls on the start date of the leave it gives the last period of leave, or if today's within a period of leave, it gives that period of leave (but in the past tense, as though it's a past period of leave). In both cases, if today's a leave day I want it to read, "You're currently on leave." I know how to check if a date's between two given dates, but as I need to check all the dates on the list I'm guessing that I need to use VLOOKUP, but can't figure out getting it to return "You're currently on leave" rather than either a true/false or a matching value in another column.
- Employees have the ability to cancel previously booked leave by selecting "Cancelled" as the type of leave (the usual two being "Annual" or "Flexi"). This changes the style of the entry in question, and also removing the time from "leave remaining" calculations. This too can cause problems for the statement on the Welcome page, because if there's no future booked leave it goes to past leave, and if the last entered period of leave is one that's been cancelled it'll display for example, "Your last booked leave was 04/09/20 to 06/09/20 which was 2 days of Cancelled leave". What I need it to do though, if the last booked leave is a cancelled one, is to give the last booked leave that's not cancelled. I know how to get the last leave period booked using the combination of
{=MAX(IF(Leave!D32:Leave!D73<TODAY(),Leave!D32:Leave!D73))}to get the start date and=VLOOKUP(U6,Leave!D32:E73,2,0)to get the last date booked, and of course checking that the leave type is "Cancelled", but what I can't figure out is how to get the last date before that one.
The possible states/error situations I've identified are:
- there is future booked leave. Response: "Your next booked leave is [DATE] to [DATE] which is [n] days of [leavetype] leave"
- there is no future booked leave; show the last leave instead. Response: "Your last booked leave was [date] to [date] which was [n] days of [leavetype] leave"
- there is no future booked leave and the last booked leave was cancelled. Show the last booked leave that wasn't cancelled. Response: "Your last booked leave was [date] to [date] which was [n] days of [leavetype] leave"
- there is no future booked leave and the last booked leave was cancelled, but the cancelled leave was the first period of booked leave. There is no prior leave before the cancelled leave. Response: "You don't have any leave booked. Why not go ahead and book some time off?"
- there is no booked leave. Response: "You've not booked any leave yet. Why not go ahead and book some time off?"
- today's date is the first day of a period of booked leave. Response: "You're currently on leave."
- today's date is within a period of booked leave. Response: "You're currently on leave."
The Welcome page
| Electronic_leave-card_v2-0_working_copy.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| N | O | P | Q | R | S | T | U | V | W | |||
| 1 | This is a hidden column | This is a hidden column | This is a hidden column | |||||||||
| 2 | ||||||||||||
| 3 | ||||||||||||
| 4 | ||||||||||||
| 5 | LAST BOOKED LEAVE | Data for information on the last period of leave booked | ||||||||||
| 6 | 19/09/2020 | First date booked | ||||||||||
| 7 | 23/09/2020 | Last date booked | ||||||||||
| 8 | 4 | Number of days | ||||||||||
| 9 | Welcome, | Kev | Annual | Type of leave | ||||||||
| 10 | NEXT BOOKED LEAVE | Data for information on the next period of booked leave | ||||||||||
| 11 | Your last booked leave was 19/09/20 to 23/09/20 which was 4 days of Annual leave. | 00/01/1900 | First date booked | |||||||||
| 12 | #N/A | Last date booked | ||||||||||
| 13 | #N/A | Number of days | ||||||||||
| 14 | You're 2 months into your leave year, and you have 16 days 00:00 (hh:mm) (64%) of your leave allowance remaining. | #N/A | Type of leave | |||||||||
| 15 | PUBLIC HOLIDAYS/DISCRETIONARY DAYS | Data for information on today's date and the next public holiday/discretionary day. | ||||||||||
| 16 | 20/09/2020 | Today | ||||||||||
| 17 | Today is 20/09/20. | #N/A | Today's holiday name | |||||||||
| 18 | The next public holiday/discretionary day is 24/12/20, a discretionary day. | 24/12/2020 | Public holiday date | |||||||||
| 19 | a discretionary day | Occasion | ||||||||||
| 20 | CURRENT LEAVE CHECK | |||||||||||
| 21 | 20/09/2020 | Today | ||||||||||
| 22 | First date booked | |||||||||||
| 23 | Last date booked | |||||||||||
| 24 | Number of days | |||||||||||
| 25 | Type of leave | |||||||||||
Welcome | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| P9 | P9 | =IFERROR(INDEX(name_list,MATCH(Configuration!E8,employee_list,0)),"colleague") |
| U6 | U6 | =MAX(IF(Leave!D32:Leave!D73<TODAY(),Leave!D32:Leave!D73)) |
| U7 | U7 | =VLOOKUP(U6,Leave!D32:E73,2,0) |
| U8 | U8 | =VLOOKUP(U6,Leave!D32:I73,6,0) |
| U9 | U9 | =VLOOKUP(U6,Leave!D32:L73,9,0) |
| O11 | O11 | =IF(ISBLANK(Leave!D32),"You've not booked any leave yet. Why not go ahead and book some time off?",IF(U11>TODAY(),"Your next booked leave is "&TEXT(U11,"dd/mm/yy")&" to "&TEXT(U12,"dd/mm/yy")&" which is "&U13&" days of "&U14&" leave.", IF(U6<TODAY(),"Your last booked leave was "&TEXT(U6,"dd/mm/yy")&" to "&TEXT(U7,"dd/mm/yy")&" which was "&U8&" days of "&U9&" leave.",""))) |
| U11 | U11 | =MIN(IF(Leave!D32:Leave!D73>TODAY(),Leave!D32:Leave!D73)) |
| U12 | U12 | =VLOOKUP(U11,Leave!D32:E73,2,0) |
| U13 | U13 | =VLOOKUP(U11,Leave!D32:I73,6,0) |
| U14 | U14 | =VLOOKUP(U11,Leave!D32:L73,9,0) |
| O14 | O14 | =IF(ISBLANK(Leave!D32),"You have "&TEXT(Leave!J28,"[h]:mm")&" (hh:mm) or "&(Leave!K28)&" of annual leave to use.",IF(Leave!W24<0,"You appear to have exceeded your leave entitlement. Please speak to your manager",IF(DATEDIF(Configuration!I12,TODAY(),"m")<1,"You're at the start of your leave year, and you have "&Leave!S23&" (hh:mm) ("&(INT(Leave!W24/Leave!J28*100))&"%) of your leave allowance remaining",IF(DATEDIF(Configuration!I12,TODAY(),"m")=1,"You're "&DATEDIF(Configuration!I12,TODAY(),"m")&" month into your leave year, and you have "&Leave!S23&" (hh:mm) ("&(INT(Leave!W24/Leave!J28*100))&"%) of your leave allowance remaining",IF(DATEDIF(Configuration!I12,TODAY(),"m")>12,"You're into a new leave year; please start a new leave card. You have "&Leave!S23&" (hh:mm) ("&(INT(Leave!W24/Leave!J28*100))&"%) of your leave allowance remaining",IF(DATEDIF(Configuration!I12,TODAY(),"m")>1,"You're "&DATEDIF(Configuration!I12,TODAY(),"m")&" months into your leave year, and you have "&Leave!S23&" (hh:mm) ("&(INT(Leave!W24/Leave!J28*100))&"%) of your leave allowance remaining.","")))))) |
| O17 | O17 | =IF(ISERROR(U17),"Today is "&TEXT(U16,"dd/mm/yy")&".",IF(U16=TODAY(),"Today is "&TEXT(U16,"dd/mm/yy")&", "&U17&".","")) |
| O18 | O18 | ="The next public holiday/discretionary day is "&TEXT(U18,"dd/mm/yy")&", "&U19&"." |
| U16,U21 | U16 | =TODAY() |
| U17 | U17 | =VLOOKUP(U16,'List Configuration'!H3:I14,2,0) |
| U18 | U18 | =MIN(IF(public_holidays>TODAY(),public_holidays)) |
| U19 | U19 | =VLOOKUP(U18,'List Configuration'!H3:I14,2,0) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| employee_list | ='List Configuration'!$E$3:$E$26 | P9 |
| holiday_name | ='List Configuration'!$I$3:$I$14 | U17, U19 |
| leave_list | ='List Configuration'!$F$3:$F$5 | P9 |
| name_list | ='List Configuration'!$F$3:$F$26 | P9 |
| public_holidays | ='List Configuration'!$H$3:$H$14 | U17, U19 |
| yes_no | ='List Configuration'!$H$3:$H$4 | U17, U19 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| O14:S16 | Cell Value | contains "You appear to have exceeded your leave entitlement. Please speak to your manager" | text | NO |
| O14 | Cell Value | contains "You appear to have exceeded your leave entitlement. Please speak to your manager." | text | NO |
The Leave page
| Electronic_leave-card_v2-0_working_copy.xlsm | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | G | I | J | K | L | |||||
| 28 | Starting balance | 185:00 | 25 days 00:00 | ||||||||||
| 29 | |||||||||||||
| 30 | First date booked | Last date booked | Half day | Number of days booked | Additional/ other hours | Total time booked | Type of leave | ||||||
| 31 | |||||||||||||
| 32 | 03/07/2020 | 07/07/2020 | No | 4 | 29:36 | Annual | |||||||
| 33 | 06/08/2020 | 06/08/2020 | No | 1 | 7:24 | Annual | |||||||
| 34 | 17/08/2020 | 20/08/2020 | Yes | 3.5 | 25:54 | Flexi | |||||||
| 35 | 04/09/2020 | 06/09/2020 | No | 2 | 14:48 | Cancelled | |||||||
| 36 | 19/09/2020 | 23/09/2020 | No | 4 | 29:36 | Annual | |||||||
| 37 | No | 1 | Annual | ||||||||||
| 38 | 1 | ||||||||||||
| 39 | 1 | ||||||||||||
| 40 | 1 | ||||||||||||
| 41 | 1 | ||||||||||||
| 42 | 1 | ||||||||||||
| 43 | 1 | ||||||||||||
| 44 | 1 | ||||||||||||
| 45 | 1 | ||||||||||||
Leave | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J28 | J28 | =J20+J22+J24 |
| K28 | K28 | =IFERROR(INT($J28/R$15)&" days "&TEXT(0.00000000001+$J28-INT($J28/R$15)*R$15,"hh:mm"),"") |
| I32:I45 | I32 | =SUM(F32-H32) |
| K32:K45 | K32 | =IF(OR(ISBLANK(D32),ISBLANK(E32)),"",(I32*$R$15)+J32) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| L32:L73 | Cell Value | contains "Cancelled" | text | NO |
| A32:C60,J32:K60,A31:E31,A61:E73,G61:G73,J61:V73,3:7,O33:O73,N34:N73,A8:W8,A1:X2,AD1:XFD2,A25:N25,A26:M26,A28:R28,M32:V60,A74:V1048576,Z76:AC76,W23,Y23:AC23,W17:AC22,W11,A11:V24,W12:X14,A27:V27,T25:V26,A29:V30,T28:V28,AD8:XFD1048576,A9:X10,W16:X16,W31:X32 | Cell Value | contains "" | text | NO |
| A32:C60,J32:K60,A31:E31,A61:E73,G61:G73,J61:V73,3:7,O33:O73,N34:N73,A8:W8,A1:X2,AD1:XFD2,A25:N25,A26:M26,A28:R28,M32:V60,A74:V1048576,Z76:AC76,W23,Y23:AC23,W17:AC22,W11,A11:V24,W12:X14,A27:V27,T25:V26,A29:V30,T28:V28,AD8:XFD1048576,A9:X10,W16:X16,W31:X32 | Cell Value | contains "ERROR" | text | NO |
| O19:P19,P18:S18,A61:E73,M33:M73,A31:C60,J32:K60,M33:N60,G61:G73,J61:N73,A25:N25,A28:M28,3:7,A19:M19,A17:S17,A18:N18,A20:Q20,O33:V33,O33:O73,N34:V73,A8:W8,A1:X2,AD1:XFD2,A27:N27,A26:M26,T25:V28,T17:V20,A29:V30,W30,A74:V1048576,Z76:AC76,A21:V24,W23,Y23:AC23 | Cell Value | <0 | text | NO |
| I32:I73 | Expression | =ISBLANK(G32) | text | NO |
| I32:I73 | Cell Value | contains "ERROR" | text | NO |
| L32:L49 | Cell Value | contains "ERROR" | text | NO |
| D32:K73,N32:O73 | Expression | =$L32="Cancelled" | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| L32:L45 | List | =leave_type |
| G32:G45 | List | Yes,No,Other |
| D32:D73 | Date | between StartDate and EndDate |
| E32:E73 | Date | between StartDate and EndDate |
Any assistance with this appreciated.
Thanks,
Bliss
