Error catching formula - help with VLOOKUP between dates and last valid date pair

BlissC

New Member
Joined
Aug 28, 2017
Messages
41
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:

=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 green coloured ones are ones I've worked out the formulas for. The red ones are the ones I'd appreciate some help with. Below are the portion of the Welcome page containing the three leave statements, and the layout of the leave request columns on the Leave page.

The Welcome page
Electronic_leave-card_v2-0_working_copy.xlsm
NOPQRSTUVW
1This is a hidden columnThis is a hidden columnThis is a hidden column
2
3
4
5LAST BOOKED LEAVEData for information on the last period of leave booked
619/09/2020First date booked
723/09/2020Last date booked
84Number of days
9Welcome,KevAnnualType of leave
10NEXT BOOKED LEAVEData for information on the next period of booked leave
11Your last booked leave was 19/09/20 to 23/09/20 which was 4 days of Annual leave.00/01/1900First date booked
12#N/ALast date booked
13#N/ANumber of days
14You're 2 months into your leave year, and you have 16 days 00:00 (hh:mm) (64%) of your leave allowance remaining.#N/AType of leave
15PUBLIC HOLIDAYS/DISCRETIONARY DAYSData for information on today's date and the next public holiday/discretionary day.
1620/09/2020Today
17Today is 20/09/20.#N/AToday's holiday name
18The next public holiday/discretionary day is 24/12/20, a discretionary day.24/12/2020Public holiday date
19a discretionary dayOccasion
20CURRENT LEAVE CHECK
2120/09/2020Today
22First date booked
23Last date booked
24Number of days
25Type of leave
Welcome
Cell Formulas
RangeFormula
P9P9=IFERROR(INDEX(name_list,MATCH(Configuration!E8,employee_list,0)),"colleague")
U6U6=MAX(IF(Leave!D32:Leave!D73<TODAY(),Leave!D32:Leave!D73))
U7U7=VLOOKUP(U6,Leave!D32:E73,2,0)
U8U8=VLOOKUP(U6,Leave!D32:I73,6,0)
U9U9=VLOOKUP(U6,Leave!D32:L73,9,0)
O11O11=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.","")))
U11U11=MIN(IF(Leave!D32:Leave!D73>TODAY(),Leave!D32:Leave!D73))
U12U12=VLOOKUP(U11,Leave!D32:E73,2,0)
U13U13=VLOOKUP(U11,Leave!D32:I73,6,0)
U14U14=VLOOKUP(U11,Leave!D32:L73,9,0)
O14O14=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.",""))))))
O17O17=IF(ISERROR(U17),"Today is "&TEXT(U16,"dd/mm/yy")&".",IF(U16=TODAY(),"Today is "&TEXT(U16,"dd/mm/yy")&", "&U17&".",""))
O18O18="The next public holiday/discretionary day is "&TEXT(U18,"dd/mm/yy")&", "&U19&"."
U16,U21U16=TODAY()
U17U17=VLOOKUP(U16,'List Configuration'!H3:I14,2,0)
U18U18=MIN(IF(public_holidays>TODAY(),public_holidays))
U19U19=VLOOKUP(U18,'List Configuration'!H3:I14,2,0)
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
employee_list='List Configuration'!$E$3:$E$26P9
holiday_name='List Configuration'!$I$3:$I$14U17, U19
leave_list='List Configuration'!$F$3:$F$5P9
name_list='List Configuration'!$F$3:$F$26P9
public_holidays='List Configuration'!$H$3:$H$14U17, U19
yes_no='List Configuration'!$H$3:$H$4U17, U19
Cells with Conditional Formatting
CellConditionCell FormatStop If True
O14:S16Cell Valuecontains "You appear to have exceeded your leave entitlement. Please speak to your manager"textNO
O14Cell Valuecontains "You appear to have exceeded your leave entitlement. Please speak to your manager."textNO


The Leave page
Electronic_leave-card_v2-0_working_copy.xlsm
BCDEGIJKL
28Starting balance185:0025 days 00:00
29
30First date bookedLast date bookedHalf dayNumber of days bookedAdditional/ other hoursTotal time bookedType of leave
31
3203/07/202007/07/2020No429:36Annual
3306/08/202006/08/2020No17:24Annual
3417/08/202020/08/2020Yes3.525:54Flexi
3504/09/202006/09/2020No214:48Cancelled
3619/09/202023/09/2020No429:36Annual
37No1 Annual
381 
391 
401 
411 
421 
431 
441 
451 
Leave
Cell Formulas
RangeFormula
J28J28=J20+J22+J24
K28K28=IFERROR(INT($J28/R$15)&" days "&TEXT(0.00000000001+$J28-INT($J28/R$15)*R$15,"hh:mm"),"")
I32:I45I32=SUM(F32-H32)
K32:K45K32=IF(OR(ISBLANK(D32),ISBLANK(E32)),"",(I32*$R$15)+J32)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L32:L73Cell Valuecontains "Cancelled"textNO
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:X32Cell Valuecontains ""textNO
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:X32Cell Valuecontains "ERROR"textNO
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:AC23Cell Value<0textNO
I32:I73Expression=ISBLANK(G32)textNO
I32:I73Cell Valuecontains "ERROR"textNO
L32:L49Cell Valuecontains "ERROR"textNO
D32:K73,N32:O73Expression=$L32="Cancelled"textNO
Cells with Data Validation
CellAllowCriteria
L32:L45List=leave_type
G32:G45ListYes,No,Other
D32:D73Datebetween StartDate and EndDate
E32:E73Datebetween StartDate and EndDate


Any assistance with this appreciated.

Thanks,

Bliss
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

earthworm

Active Member
Joined
May 19, 2009
Messages
368
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You have posted too long . Kindly share excel file and highlighted the answer that you want in green . you can manually enter the answer.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,296
Messages
5,571,396
Members
412,386
Latest member
Yasaman
Top