CODEWITHSUNDEEP
excel
user3206333
user3206333

Reputation: 3

Excel - Finding the next day that is not in a list

I have a list of dates (School Holidays) in chronological order. I'd like to give it a date, and have the formula return the next day that is NOT in the list.

This is my list of dates:

January 1
January 2
January 3
January 28
February 11
February 15 etc

January 1 should return January 4 January 27 on the other hand, should return January 27 (because it's not on the list) January 28 should return January 29, etc

I've looked and looked, but can't seem to find anything related to this. Do I need to do something in VBA? Or is there an easier way using just functions?

Thanks! Dan

Upvotes: 0

Views: 271

Answers (2)

barry houdini
barry houdini

Reputation: 46391

Which version of Excel are you using? In Excel 2010 or later versions you can use WORKDAY.INTL function, e.g. with your holiday dates in A1:A20 and your reference date in D1

=WORKDAY.INTL(D1-1,1,"0000000",$A$1:$A$20)

That uses D1-1 and finds the next "working day" after that, with "0000000" defining all days as working days (except the holidays listed in A1:A20), so that may be D1 itself or the next non-holiday after that

Upvotes: 2

Edward
Edward

Reputation: 8606

The easiest way is to write a VBA Function.

Steps:

1: Name the range of holidays. Do this by selecting the holiday column, and entering "Holidays" in the name box.

Excel Name Box

2: Display the VBA editor by pressing alt+F11

3: In VBA: Insert > Module

Excel Insert Module

4: Enter this code

Option Explicit

Public Function NextSchoolDay(StartDate As Date)

Dim Holidays As Range
Set Holidays = Range("Holidays")

NextSchoolDay = StartDate
Do
    NextSchoolDay = NextSchoolDay + 1
Loop Until Not IsNumeric(Application.Match(CLng(NextSchoolDay), Holidays, 0))

End Function

5: You can now use the formula =NextSchoolDay(Date)

Excel NextSchoolDay Formula

Upvotes: 0

Related Questions