How do I count the next 10 days from a given date? But only if they were
Mondays and Thursdays? In the example below, I want to count the 10 Mondays
and Thursdays from 1st October.

For example:

A1 = Mon B1 = Thu

A2 = 01/10/2007 B2 = 10

On Mon, 8 Oct 2007 12:04:04 -0700, Annie99
wrote:

How do I count the next 10 days from a given date? But only if they were
Mondays and Thursdays? In the example below, I want to count the 10 Mondays
and Thursdays from 1st October.

For example:

A1 = Mon B1 = Thu

A2 = 01/10/2007 B2 = 10


Your question would be more clear to me if you also gave the result you expect
from this calculation.
--ron

Is your date in A2 always a Monday? If not, how do you want compute the
first Monday, the Monday following your date or the Monday preceding your
date?

"Annie99" wrote in message
...
How do I count the next 10 days from a given date? But only if they were
Mondays and Thursdays? In the example below, I want to count the 10
Mondays
and Thursdays from 1st October.

For example:

A1 = Mon B1 = Thu

A2 = 01/10/2007 B2 = 10

One possible way

=SUM(INT((A2+B2-WEEKDAY(A2+1-{2;5})-A2+8)/ 7))


where you can change the {2;5} based on the below


1 Sun
2 Mon
3 Tue
4 Wed
5 Thu
6 Fri
7 Sat

The above formula returns 3 using 1/10/07 (US date format Jan 10 2007) in A2
and 10 in B2


--


Regards,


Peo Sjoblom


"Annie99" wrote in message
...
How do I count the next 10 days from a given date? But only if they were
Mondays and Thursdays? In the example below, I want to count the 10
Mondays
and Thursdays from 1st October.

For example:

A1 = Mon B1 = Thu

A2 = 01/10/2007 B2 = 10

Yes, the date I want is 5th November 07. The 10 Mon and Thu would be 4th,
8th, 11th, 15th, 18th, 22nd, 25th, 29th, 1st Nov, 5th Nov

"Ron Rosenfeld" wrote:

On Mon, 8 Oct 2007 12:04:04 -0700, Annie99
wrote:

How do I count the next 10 days from a given date? But only if they were
Mondays and Thursdays? In the example below, I want to count the 10 Mondays
and Thursdays from 1st October.

For example:

A1 = Mon B1 = Thu

A2 = 01/10/2007 B2 = 10


Your question would be more clear to me if you also gave the result you expect
from this calculation.
--ron

A1: holds date

B1: =A1-MOD(A1-2,7)+(WEEKDAY(A1)<2)*7
B2: =B1+3+(WEEKDAY(B1+3)<2)

copy from B2 down to B10


"Annie99" wrote:

How do I count the next 10 days from a given date? But only if they were
Mondays and Thursdays? In the example below, I want to count the 10 Mondays
and Thursdays from 1st October.

For example:

A1 = Mon B1 = Thu

A2 = 01/10/2007 B2 = 10

Thanks.
Rather than showing each date, I just need to display the final date in a
cell. So in my example, it would be 5th November 07 as its the 10 day after
the 1st October (Mondays and Thursdays only)

"Teethless mama" wrote:

A1: holds date

B1: =A1-MOD(A1-2,7)+(WEEKDAY(A1)<2)*7
B2: =B1+3+(WEEKDAY(B1+3)<2)

copy from B2 down to B10


"Annie99" wrote:

How do I count the next 10 days from a given date? But only if they were
Mondays and Thursdays? In the example below, I want to count the 10 Mondays
and Thursdays from 1st October.

For example:

A1 = Mon B1 = Thu

A2 = 01/10/2007 B2 = 10

On Mon, 8 Oct 2007 12:43:01 -0700, Annie99
wrote:

Yes, the date I want is 5th November 07. The 10 Mon and Thu would be 4th,
8th, 11th, 15th, 18th, 22nd, 25th, 29th, 1st Nov, 5th Nov

"Ron Rosenfeld" wrote:

On Mon, 8 Oct 2007 12:04:04 -0700, Annie99
wrote:

How do I count the next 10 days from a given date? But only if they were
Mondays and Thursdays? In the example below, I want to count the 10 Mondays
and Thursdays from 1st October.

For example:

A1 = Mon B1 = Thu

A2 = 01/10/2007 B2 = 10


Your question would be more clear to me if you also gave the result you expect
from this calculation.
--ron


You could adapt my WrkDay UDF to do this. It is a UDF that mimics Excel's
Workday function, except that it allows you to specify which days of the week
are "weekend days".

To use the formula, you would enter it as:

=wrkday(A2,B2,,1,3,4,6,7)

A2 is your Start Date
B2 is the number of days you wish to add
Note the [,,]. The missing argument is an optional Holiday list, which makes
more sense in the context of the WrkDay function, but you could use it if there
were occasional Mons or Thursdays you did not want to count.

The 1,3,4,6,7 represent the days of the week Other than Mon or Thu.

(1 = Sunday, 3 = Tuesday, etc.)

To enter this UDF, <alt-F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

The routines also include one that mimics the Excel NetWorkdays function, that
is required for the wrkday function.

Also, the Number of Days can be positive or negative.

Let me know if this does what you want.

================================================== ==
Function NWrkDays(StartDate As Date, EndDate As Date, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0, _
Optional WeekendDay_4 As Integer = 0, _
Optional weekendday_5 As Integer = 0) As Long
' Sunday = 1; Monday = 2; ... Saturday = 7

'credits to Myrna

Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean

DoHolidays = Not (Holidays Is Nothing)

SD = StartDate: ED = EndDate
If SD ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If

w = Weekday(SD - 1)
For i = SD To ED
Count = Count + 1
w = (w Mod 7) + 1
Select Case w
Case WeekendDay_1, WeekendDay_2, WeekendDay_3, _
WeekendDay_4, weekendday_5
Count = Count - 1
Case Else
If DoHolidays Then
If IsNumeric(Application.Match(i, Holidays, 0)) Then _
Count = Count - 1
End If
End Select
Next i
If NegCount = True Then Count = -Count
NWrkDays = Count
End Function

Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0, _
Optional WeekendDay_4 As Integer = 0, _
Optional weekendday_5 As Integer = 0) As Date

' Sunday = 1; Monday = 2; ... Saturday = 7

Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim temp As Long, SD As Date, ED As Date

Stp = Sgn(NumDays)

'Add NumDays
TempDate = StartDate + NumDays

'Add Non-Workdays

Do While Abs(NumDays) < temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)

temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, _
WeekendDay_3, WeekendDay_4, weekendday_5)
TempDate = TempDate + NumDays - Stp * (temp)
Loop

WrkDay = TempDate
End Function
==========================================
--ron