LastFriday function, exceptions, & year end

[Guest]

Some time ago Klatuu helped me with a nice function that returns the last
Friday of any date entered. I've been working with it and it's great. Now I'm
trying to code some procedures to handle two exceptions and deal with a year
change problem.

Basically, I have a form with a text box where the user inputs (or selects
from a popup calendar, I haven't decided) a payment date, call it PayDate.
Using that I want to determine a couple of things. First, the last Friday of
the month for that date. With Klatuu's help that's the easy part. This is
used as the closing date for monthly fiscal transactions.

Now, we have two exceptions. If the month is either March or September, the
closing will actually be on the next to last Friday. Looking at what results
using the VBA immediate window, I'm playing with something like this for the
exceptions

?"09/"&(Format(LastFriday(#9/12/2006#),"dd")-7)&"/2006
which returns what I want, 9/22/2006

I can compose a procedure that will give me the correct closing dates for
the PayDate and the two exceptions, whether using if...then or case methods,
I'm not sure. I guess I'll try both and choose what is more elegant and brief.

Then, I want to output the actual start and end dates of each period to use
as a filter. I figure the start date as the prior month's closing date plus
one, and the ending date as the closing date. I have to figure that when the
PayDate is in January then the prior month's closing date has to subtract one
from the year as well as add one to the day.

I would appreciate any direction or suggestions on how to handle this.

Ray S.

 

[Guest]

Hello again.
Sounds like you are pretty much on the right track. You have the exceptions
under control.
The Year End is not a problem. It will take care of itself. For example
let's say you want to figure the starting day for January '06. Pick any day
in January ' 06, subtract one month from it - =dateadd("m", -1,SomeDate) and
you will get the same day for December of '05.

Now, here is how you find the beginning date for your fiscal period:
= dateadd("d",1,lastfriday(dateadd("m",-1,lastfriday(SomeDate))))
Works even for January.

 

[Guest]

Hi Klatuu,

I am interesting for this lastfriday function, could you please post for
reference?

Thanks in advance!

 

[Guest]

Happy to. This function returns the last Friday of the month for the month
entered. It does not matter what day your date passes, only the month and
year.

Function LastFriday(dtmBaseDate As Date) As Date
'Finds the date of the Last Friday of the month for the date entered
Dim intCurrMonth As Integer
Dim intNextMonth As Integer
Dim intNextYear As Integer
Dim blnAllDone As Boolean
' Find the Current and Next Months
intCurrMonth = Month(dtmBaseDate)
intNextYear = year(dtmBaseDate) + 1
intNextMonth = intCurrMonth + 1
' Find the Friday for the date passed
dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate),
dtmBaseDate)
If Month(dtmBaseDate) = intNextMonth Then
'The Friday for the date entered is in the next month
'so subtract a week and it will be the last Friday
LastFriday = DateAdd("ww", -1, dtmBaseDate)
Exit Function
End If
If Month(dtmBaseDate) <> intCurrMonth Then
'The Friday for the date entered is in the previous month
'so add a week to get back to current month
'Used <> instead of < because in Jan(1) you can end up in Dec(12)
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
End If
blnAllDone = False
Do Until blnAllDone
dtmBaseDate = DateAdd("ww", 1, dtmBaseDate)
blnAllDone = Month(dtmBaseDate) = intNextMonth Or _
year(dtmBaseDate) = intNextYear
Loop 'Until blnAllDone
LastFriday = DateAdd("ww", -1, dtmBaseDate)
End Function

 

[Guest]

Thanks Klatuu. I worked on this with your suggestion and came up with this:

If datepart("m",Forms!frm_Journal_ID!txtInvDate)=3 or
datepart("m",Forms!frm_Journal_ID!txtInvDate)=9 then

CloseDate =
datepart("m",#Forms!frm_Journal_ID!txtInvDate#)&"/"&datepart("d",(LastFriday(#Forms!frm_Journal_ID!txtInvDate#))-7)&"/"&datepart("yyyy",#Forms!frm_Journal_ID!txtInvDate#)
StartDate =
dateadd("d",-6,lastfriday(dateadd("m",-1,lastfriday(#Forms!frm_Journal_ID!txtInvDate#))))
Else
CloseDate =
datepart("m",#Forms!frm_Journal_ID!txtInvDate#)&"/"&datepart("d",(LastFriday(#Forms!frm_Journal_ID!txtInvDate#)))&"/"&datepart("yyyy",#Forms!frm_Journal_ID!txtInvDate#)
StartDate =
dateadd("d",1,lastfriday(dateadd("m",-1,lastfriday(#Forms!frm_Journal_ID!txtInvDate#))))

End If

Do you think this will work OK? I tried to account for the two exception
months and referenced the text box in the form that will input the reference
date. I'm not sure if I have to concatenate the # symbols.

Ray S.

 

[Guest]

You are making more complex than it needs to be. If your date is correctly
formatted in your text box, you don't need the #, and you don't need all the
other stuff you are doing. These two lines will return your close and start
dates, but do not address the exceptions:
CloseDate = LastFriday(Forms!frm_Journal_ID!txtInvDate)
StartDate = dateadd("d",1,lastfriday(dateadd("m",-1, _
lastfriday(Forms!frm_Journal_ID!txtInvDate))))

Now, for exceptions. I do these afterwards, because it is only necessary to
back up the closing dates for March and September. This also means we will
have to adjust the start dates for April and October, right? So all that is
necessary is to subtract one week for the exception periods.

lngException = Month(CloseDate)
If lngException = 3 or lngException = 9 Then
CloseDate = DateAdd("ww", -1, CloseDate)
End If
If lngException = 4 or lngException = 10
StartDate = DateAdd("ww", -1, StartDate)
End If

 

[Guest]

I posted a question after your response. How would I make this a function
that I could save in a module and call whenever needed, something like:

Function GetBeginDate(???) As Date

If (Month = 3 Or Month = 9) Then
LastFriday(variable).AddDays (-6)
Else
LastFriday (variable).AddDays (1)
End If

End Function

Function GetEndDate(???) As Date

If (Month = 3 Or Month = 9) Then
LastFriday(variable).AddDays (-7)
Else
LastFriday (variable)
End If