vba coding question

  • Thread starter Thread starter thomas donino
  • Start date Start date
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
 
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.
 
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
 
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
 
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...
 
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).
 
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
 
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
 
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.
 
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
 
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?
 
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 ..........
 
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
 
Back
Top