Skip a day of the week

[Darbar]

Hi All,

I have a situation like I need to skip Thursday(weekly off) from weekdays(sat & sunday are included). For example :


Today is 24 May 2005 and if I add 4 to this date, it will give 28 May 2005. But in this I have one Thursday, it should ignore Thursday and I need a result like 29 May.

Clear explanation of my query


Say if cell A1 is having todays date and A2 is having number 4 then I need the specified result in cell C1. And the result should not include Thursdays.

Can anyone please help me regarding this issue.

Thanks in Advance

Darbar

[Flintstone]

Hi Darbar:

This is the best I could come up with; I hope its good enough.

=IF(A2+WEEKDAY(A1,1)=5,A2+A1+1,A2+A1)

Or

=IF(A2+WEEKDAY(A1)=1,A1+A2,IF(A2+WEEKDAY(A1)=2,A1+A2,IF(A2+WEEKDAY(A1)=3,A1+A2,IF(A2+WEEKDAY(A1)=4,A1+A2,IF(A2+WEEKDAY(A1)=5,A1+A2+1,IF(A2+WEEKDAY(A1)=6,A1+A2,IF(A2+WEEKDAY(A1)=12,A1+A2,"")))))))

Place either, or in C1.

The formulas work like you requested, "skipping over Thursday".

Formulas only work for the first Thursday, that means A2 should not be a number greater than 6.

Problem is you can't remove Thursday's from the calendar, and the date functions won’t allow you to ignore them either. Or more to the fact is that I just don't have enough know how to get around it.

Matt

[mangesh_yadav]

Use the following function. Enter the following code in a standard module:

Function myWorkDay(myDate As Date, AddDays, LeaveDay)

    If AddDays = 0 Then
        myWorkDay = myDate
        Exit Function
    End If

    For j = 1 To 7
        If Choose(j, "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday") = LeaveDay Then
            LD = j
        End If
    Next j

    i = 1
    Do While i <= AddDays
        myWorkDay = myDate + i
        If WorksheetFunction.Weekday(myWorkDay) = LD Then
            AddDays = AddDays + 1
        End If
        i = i + 1
    Loop

End Function

Usage:
Enter in cell C1
=myWorkDay(A1,A2,"Thursday")


Mangesh

[Darbar]

Thank you very much Mangesh !!!!

I got the perfect result. Thanks a lot.


DARBAR

[mangesh_yadav]

Appreciate the feedback. Thanks

Mangesh

[mangesh_yadav]

Found another solution through this thread:
http://excelforum.com/showthread.php...d=1#post992503

=$A$1+IF(B1=0,0,SIGN(B1)*SMALL(IF((WEEKDAY($A$1+SIGN(B1)*(ROW(INDIRECT("1:"&ABS(B1)*10))),2)<>4)*ISNA(MATCH($A$1+SIGN(B1)*(ROW(INDIRECT("1:"&ABS(B1)*10))),0,0)),ROW(INDIRECT("1:"&ABS(B1)*10))),ABS(B1)))

confirm with control shift enter

A1 is the date, B1 is the number of days to add.



Mangesh