We help IT Professionals succeed at work.

Last Working Day :: Find a value upward

suvmitra
suvmitra used Ask the Experts™
on
Hi,

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.
Comment
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
    
Loop


End Function

Open in new window

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

=MAX(IF(TEXT($A$1:A1,"DDD")=$B$1:B1,$A$1:A1))

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

Author

Commented:
Thank you all :)