vba coding question

T

thomas donino

I have a date in a cell which I need to check for whether or not it is a
weekend day or a holiday. I have a function to do each, my question is how do
I construct the loop to check first if its a weekend day and if so run again
to see if the day prior is a weekend day and then if that is also a weekend
day to check if day prior again is a holiday. If it is, return the next prior
day and if not then return that day. I already built the Isweekend and
Isholiday functions.

Thank you in advance
 
J

Joel

What was wrong with the code I sent in your last posting? The requirments
are very similar to this requuest. did your requirements change or was there
something wrong with the rpevious code.
 
T

thomas donino

I couldnt get it to work and instead built 5 custom functions to perform
various tasks. This task is to identify if the target date is a business day.
I am doing this by using the IsWeekend function first. If true, then I need
to check the date prior, if it is also a weekend then I check the date prior
to that to see if its a holiday,if it wasn't it returns that day. If the day
prior was a holiday then I return the day prior to that day.

Here are the functions code;

Public Function IsHoliday(dttoCheck As Date) As Boolean

Dim myval As Variant
myval = Application.Match(CLng(dttoCheck), Range("HolidayDates"), 0)

If IsError(myval) Then
IsHoliday = False
Else
IsHoliday = True
End If

End Function


Public Function IsWeekend(dttoCheck As Date) As Boolean
If Weekday(dttoCheck) = 1 Or Weekday(dttoCheck) = 7 Then
IsWeekend = True
Else
IsWeekend = False
End If
End Function

I did it this way because I felt I could use these functions in other projects
 
R

Ron Rosenfeld

I have a date in a cell which I need to check for whether or not it is a
weekend day or a holiday. I have a function to do each, my question is how do
I construct the loop to check first if its a weekend day and if so run again
to see if the day prior is a weekend day and then if that is also a weekend
day to check if day prior again is a holiday. If it is, return the next prior
day and if not then return that day. I already built the Isweekend and
Isholiday functions.

Thank you in advance
Click to expand...

Excel has this function either built-in (Excel 2007+) or as part of the
analysis tool pack.

For a worksheet function, with your date to process in A1, and Holidays being a
named range of holiday dates, merely use:

=WORKDAY(A1+1,-1,Holidays)

--ron
 
T

thomas donino

thank you

Ron Rosenfeld said:
Excel has this function either built-in (Excel 2007+) or as part of the
analysis tool pack.

For a worksheet function, with your date to process in A1, and Holidays being a
named range of holiday dates, merely use:

=WORKDAY(A1+1,-1,Holidays)

--ron
Click to expand...
 
R

Rick Rothstein

I think this will do what you want...

Function GetDate(D As Date) As Date
GetDate = D + (Weekday(D, vbMonday) > 5) + (Weekday(D, vbMonday) > 6)
GetDate = GetDate + IsHoliday(GetDate)
End Function

Note that I do not make use of your IsWeekend function in this code... I
only use your IsHoliday function. Also note those plus signs are all correct
(VB True values evaluate to -1, so adding them subtracts days).
 
J

Joel

I see you used pieces of my previous code. And I like that you put the
holidays on the worksheet in a named range. I used an Array because it was
easier to do it in array when posting the results. You still will need a
very complicated IF statement in the workbook to get the results you need.

I combined your code and my code together to get 3 functions. Make sure the
cell that is returned s formated as a date otherwise you will get a number.
the code is tested and should work.. I also tested the last code. that is
why I'm thinking you either are not passing a real date into the function, or
you simply have to format the cell as a date. You were probably getting a
number results.


Public Function IsHoliday(dttoCheck As Date) As Boolean

Dim myval As Variant
Set myval = Range("HolidayDates").Find(what:=dttoCheck, _
LookIn:=xlValues, lookat:=xlWhole)

If myval Is Nothing Then
IsHoliday = False
Else
IsHoliday = True
End If

End Function


Public Function IsWeekend(dttoCheck As Date) As Boolean
If Weekday(dttoCheck) = 1 Or Weekday(dttoCheck) = 7 Then
IsWeekend = True
Else
IsWeekend = False
End If
End Function

Function Prior3Days(Target As Date) As Date

' only process data theat is a date
If IsDate(Target) Then

Prior3Days = Target
CountDays = 3
Do While CountDays > 0
'subtract 1 day
Prior3Days = Prior3Days - 1
'check if the day is a weekend
If Not IsWeekend(Prior3Days) Then

'check if the day is a holiday

If Not IsHoliday(Prior3Days) Then
CountDays = CountDays - 1
End If
End If
Loop
End If
End Function
 
T

thomas donino

This returns whatever date is in the cell. I am looking to take the month
from the date and find the last day of that month
 
T

thomas donino

Rick, my prior reply was to my question as to whether or not there is a
function that returns the last day of a given month. Your code was for my
first post I guess. I was already instructed to use the =workday function,
which is working great.
 
R

Ron Rosenfeld

Does there happen to be a last day of the month function too?
Click to expand...

=eomonth(some_date,0) will give the last day of the month of some_date

If either of these give the NAME error, check HELP for the function for how to
correct it.
--ron
 
J

Joel

Rick How do you handle the case when the data is a monday and a holiday is on
Friday. Or the date is Saturday and and the prior Thursday is Thanksgiving?
 
T

thomas donino

Here are two functions, Isholiday, which works with IsBizDay.
IsBizDay returns true if its not a weekend day or a holiday. You will need a
list of holidays in a column for this to work

Public Function IsHoliday(dttoCheck As Date) As Boolean

Dim myval As Variant
myval = Application.Match(CLng(dttoCheck), Range("HolidayDates"), 0)

If IsError(myval) Then
IsHoliday = False
Else
IsHoliday = True
End If

End Function
Public Function IsBizDay(Targdate As Date) As Boolean

Dim dt As Variant

'dt = DateValue(Weekday(Targdate))
dt = Weekday(Targdate)
If dt > 1 And dt < 7 And (IsHoliday(Targdate) = False) Then
IsBizDay = True
Else
IsBizDay = False
End If

End Function


Then you can use If IsBizDay then ..........
 
R

Rick Rothstein

Good question! I think this (untested) code should handle all the
possibilities...

Function GetDate(D As Date) As Date
GetDate = D
Do
GetDate = GetDate + (Weekday(GetDate, vbMonday) > 5) + _
(Weekday(GetDate, vbMonday) > 6)
GetDate = GetDate + IsHoliday(GetDate)
Loop While Weekday(GetDate, vbMonday) > 5 Or IsHoliday(GetDate)
End Function
 
Top