# Last Working Day :: Find a value upward

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.

Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
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
``````

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

Commented:
Thank you all :)