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

#### BlissC

##### New Member
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)
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

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
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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### earthworm

##### Well-known Member
You have posted too long . Kindly share excel file and highlighted the answer that you want in green . you can manually enter the answer.

Replies
5
Views
616
Replies
5
Views
248
Replies
1
Views
277
Replies
3
Views
1K
Replies
7
Views
504

1,195,636
Messages
6,010,845
Members
441,569
Latest member
PeggyLee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back