Skip a day of the week
 

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,A 1+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


--
Flintstone
------------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310
View this thread: http://www.excelforum.com/showthread...hreadid=373356

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


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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=373356

Thank you very much Mangesh !!!!

I got the perfect result. Thanks a lot.


DARBAR


--
Darbar
------------------------------------------------------------------------
Darbar's Profile: http://www.excelforum.com/member.php...o&userid=23665
View this thread: http://www.excelforum.com/showthread...hreadid=373356

Appreciate the feedback. Thanks

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=373356

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+SI GN(B1)*(ROW(INDIRECT("1:"&ABS(B1)*10))),2)<4)*ISN A(MATCH($A$1+SIGN(B1)*(ROW(INDIRECT("1:"&ABS(B1)*1 0))),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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=373356