We help IT Professionals succeed at work.

Last Working Day :: Find a value upward

suvmitra used Ask the Experts™

I have a situation. Lets consider the below example.

Col A                   Col B
1-Aug-11             Holiday
2-Aug-11             Tue
3-Aug-11              Wed
4-Aug-11              Thu
5-Aug-11              Fri
6-Aug-11              Holiday
7-Aug-11              Holiday
8-Aug-11              Mon

Now I need to have a vba code or formula which will identify the last  working day.
eg. if my present working day (Today) is 8-Aug-11 then my last working day was 5-Aug-11. Similarly If the present working day is 2-Aug-11 then last working day should return 29-Jul-2011.

Please help on this.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Here's a function that does it.  Your workbook is attached.
Public Function GetLastWorkDay(LastDay As Range) As String

Dim StillLooking As Boolean
    StillLooking = True
Dim CurrentOffset As Long
    CurrentOffset = -1
Do While StillLooking = True
    If LastDay.Offset(CurrentOffset, 1).Value Like "*Holiday*" = False Then
        GetLastWorkDay = LastDay.Offset(CurrentOffset, 0).Value
        StillLooking = False
    End If
    If LastDay.Offset(CurrentOffset, 0).Row = 1 Then
        GetLastWorkDay = "N/A"
    End If

    CurrentOffset = CurrentOffset - 1

End Function

Open in new window

Assuming your data starts in A1 you can enter this ARRAY formula in C2 and copy it down.


This will give the last working day for each data IF it is present in the list.

Remember that to enter an array formula you have to press Ctrl-shift-enter. You should be able to see ellipses {} around the formula. If you do not see the ellipses then
select the cell
press F2
press Ctrl-shift-enter


Thank you all :)