How do you find the last business day in MS Excel

Visual Basic would be great also to see what is available..I should have mentioned earlier AI have no preference, but since my other code is VB it would make sense to have VB..

Check this out, it is an enhanced version of the built-in Weekday function. http://www.cpearson.com/excel/BetterWorkday.aspx

Excel 2013 added the WORKDAY.INTL function, among others. It is like WORKDAY, but also allows you to specify which days of the week constitute rest days.

Visual Basic would be great also to see what is available..I should have mentioned earlier AI have no preference, but since my other code is VB it would make sense to have VB.

Thanks for your clarification on this point.

Did you miss my other query about your definition of "last business day"?

Hey Fanpages,

Sorry I missed that...

I need the prior business day..business day defined as Monday thru Friday unless a Holiday...

Hi,

Thanks again.

Obviously, public holiday dates differ annually, & from country to country.

Do you have a pre-defined list of dates of non-business days for the duration that this workbook will be in use?

yes...I have those in a tab called public holidays

Here is a user-defined function that will return the closest business day to a given date. It assumes Saturday and Sunday are weekend, and will take into account a specified range of holidays. It will either look forward or backwards as necessary. You can use it with a worksheet formula like:
=BusinessDay(A1,Holidays,FALSE)            looks backwards for business day

Function BusinessDay(ByVal vDate As Variant, ByVal Holidays As Variant, ByVal bLookAhead As Boolean) As Date
'Returns the closest business day to vDate. Looks ahead if bLookAhead is True, looks back if False.
Dim Dat As Date
Dim v As Variant
Dat = CDate(vDate)
Do
    If Weekday(Dat, vbMonday) < 6 Then
        On Error Resume Next
        v = Application.Match(CLng(Dat), Holidays, 0)
        On Error GoTo 0
        If IsError(v) Then Exit Do
    End If
    Dat = Dat + IIf(bLookAhead, 1, -1)
Loop
BusinessDay = Dat
End Function

Select allOpen in new window

For your particular question (looking backwards from the date in cell A1 with a named range Holidays containing holiday dates), I suggest using a formula like:
=BusinessDay(A1-1,Holidays,FALSE)
BusinessDayQ28183932.xlsm

yes...I have those in a tab called public holidays

Lucky I asked that then.  Were you going to mention it if I hadn't? :)

Brad has offered you a solution now, so it looks like I need not bother doing so.

Please let him/us know if this does not meet your requirements.

I added all the ByVal to the first line of the UDF because the MATCH function kept returning error values during debugging. Converting Dat into a Long fixed that problem, so the ByVal really aren't required.

Function BusinessDay(vDate As Variant, Holidays As Variant, bLookAhead As Boolean) As Date
'Returns the closest business day to vDate. Looks ahead if bLookAhead is True; looks back if False.
Dim Dat As Date
Dim v As Variant
Dat = CDate(vDate)
Do
    If Weekday(Dat, vbMonday) < 6 Then
        On Error Resume Next
        v = Application.Match(CLng(Dat), Holidays, 0)
        On Error GoTo 0
        If IsError(v) Then Exit Do
    End If
    Dat = Dat + IIf(bLookAhead, 1, -1)
Loop
BusinessDay = Dat
End Function

Select allOpen in new window

I am getting a result of 07/04/2013 when A1 is 7/5/2013 ?  I was expecting 7/3 since 07/04/2013 is in my Holiday spreadsheet tab..Can you take a look at the attached...
ee-biz-day.xlsm

Holidays needs to be a named range for the formula to work as expected.

Brad
ee-biz-dayQ28183932.xlsm

Can you explain a little on the mechanics of the 2 below lines ?  The code works great, but I want to understand the innards of the code so I can increase my knowledge...thx

I think for the at= line you are determining to back or forward depending on if true/false came into the udf ?  For the Application Match I am struggling a little bit on...

v = Application.Match(CLng(Dat), Holidays, 0)
at = Dat + IIf(bLookAhead, 1, -1)

the CLng function in VBA converts its parameter to a Long integer. I found that passing a Date variable to MATCH would always return an error value, even if the date was present in Holidays.

This seems to be a common issue if you "Google" for results containing pertinent keywords (as I have just looked at, briefly); EXCEL VBA DATE MATCH LONG.

I would think that this is due to a Date data type being stored in 8 bytes (due to floating point element that contains the Time portion), whereas if you are just looking for a Date within a cell, then only 4 bytes are required; hence, no matches are found.

Did you try searching of the required date with a prefix (TIMEVALUE) of (a space character) & "00:00:00"?

I did test a combined date and time before posting, but only after putting in the CLng fix.

Brad

Nigel,
I like your explanation for why CLng works.

I'm pretty sure I've made the Date variable & MATCH function mistake before, and am just about as certain I'll make it again. I hope your explanation will stick with me though, so I won't spin my wheels quite so long in the future.

Thanks!

Brad

:) No problem, Brad.

Even if my theory is incorrect, at least you'll have somebody to blame for the mistake next time! ;)

Sorry..it took me long in the points.  I wanted to spend some time on reviewing it line by line.  I really enjoyed all the input and the detailed articulated explanation of Match.  Thanks for your efforts and attention to my questions.