[AccessD] Every other Friday

OK then if you want to change the stipulated use case, how about something like:

Function AvailableForMeeting(myDate As Date,Optional OppositeWeek as Booelan = False) As Boolean

If Oppositeweek then
     Select Case myDate Mod 14
        Case 0 To 3, 7 To 11
           AvailableForMeeting = True
        Case Else
           AvailableForMeeting = False
     End Select
Else
     Select Case myDate Mod 14
        Case 0 To 4, 7 To 10
           AvailableForMeeting = True
        Case Else
           AvailableForMeeting = False
     End Select
End if

End Function

Of course, the preferred solution is still a date table which lets you block out Good Friday etc 
and a function which would reset all future "Friday Offs" to the opposite week.   But that 
wasn't what was asked for.


On 22 Feb 2013 at 1:56, William Benson (VBACreations. wrote:

> I am suspicious of any solution which does not allow a week in which "the"
> Friday is skipped and the recurring engagement pushed to every other Friday
> beginning after a skipped week. That is how my wife's piano lessons go.
> Every other Friday then she thinks of something she would rather do on one
> of her engaged weeks, and it all time shifts one week to the right.
> B
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
> Sent: Thursday, February 21, 2013 5:16 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Every other Friday
> 
> Thbat doesn't return a Julian Date.  It returns the day of the year.  Since
> yeasr start on different days of the week, that is no real help.
> 
> Julian Date is the number of days since noon on 1 Jan 4173 BCE.
> (Currently 2456345) so you could use that, but it is simpler to use the
> Access built in Date Type which stores the date as the number of days since
> 30 Dec 1899
> 
> I'm assuming that the original question was along the lines of:
> A day is suitable for a meeting if it falls on Monday to Thursday or a
> non-furlough Friday.
> 
> For the  reference  Furlough Friday:    Datevalue("22 feb 2012") mod 14 = 11
> Therefore suitable days are MOD  0 - 4 (Mon-Fri of Week 1) and 7 - 10 (Mon -
> Thur of week 2).  Hence:
> 
> Function AvailableForMeeting(myDate As Date) As Boolean
>     Select Case myDate Mod 14
>        Case 0 To 4, 7 To 10
>           AvailableForMeeting = True
>        Case Else
>           AvailableForMeeting = False
>     End Select
> End Function
> 
> 
> On 21 Feb 2013 at 13:56, Dan Waters wrote:
> 
> > Hi David,
> > 
> > You might create a formula of some kind using Julian dates.  With 
> > these dates the days of the year are numbered 1 - 365 (or 1 - 366).  
> > Luckily, each week is 7 days, so some math might do the trick.
> > 
> > I copied this function from an Access forum:
> > 
> > Function CDate2Julian(MyDate As Date) As String
> >     CDate2Julian = Format(MyDate - DateSerial(Year(MyDate) - 1, 12, 
> > 31),
> > "000")
> > End Function
> > 
> > Good Luck!
> > Dan
> > 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David 
> > McAfee
> > Sent: Thursday, February 21, 2013 1:23 PM
> > To: Access Developers discussion and problem solving
> > Subject: [AccessD] Every other Friday
> > 
> > Okay, I have a question for the Access community :)
> > 
> > 
> > If every other Friday (starting Feb 22nd 2013) is a Furlough Friday 
> > (office is closed), is there a way to determine if a given date such 
> > as August 16th
> > 2013 is available for a meeting?
> > 
> > I'm thinking something to do with Mod 14 maybe?
> > 
> > SQL or VBA answers are fine.
> > 
> > Thanks in advance.
> > 
> > David McAfee
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>