Sir JLatham,

i had attached from my email message yesterday the file for your kind help
to review.

I am concerned firstly on the structuring of the lookups for the holidays. I
had no problem with your UDF ....detereaster..thanks for this one..

.....and the consideration of non-workday for saturdays and mondays -if the
holiday falls on friday and sundays,

.....except for easter sunday, in the following monday has work.

drilbler2


"JLatham" wrote:

Not any more - I've been retired from the AF for some years now.

"dribler2" wrote:

thank you sir,
tomorrow, finish or not, i will attach the file in an email.
Do you still work with the Airforce?

"JLatham" wrote:

Thank you. Believe it or not, that came out of some help I provided to a
lady in New Zealand who was taking an Excel class and they were given the
overall formula (from what looked like the days of FORTRAN) and told to make
it work in Excel. She got stumped and one point and I helped her finish it
up.

I would be glad to look over the file when you are finished with it - you
may attach it to an email and send it to (remove spaces for real email)
HelpFrom @ jlathamsite.com

Happy New Year

"dribler2" wrote:

Hello Sir Jerry Latham,

I am amazed by your respectful and holy UDF. Right now I am expanding the
workbook you gave me for my 6 to 10 years calendar. I am trying now to affix
another UDF I gathered from this forum (fnworkday] and build another sheet(s)
for the desired workdays scheduling.

I will transmit to you the file before new year for your fervent auditing,
if possible.

more power
dribler2 : romelsb @ 4pinoy.driller [art n P] /DynamicDuo
"JLatham" wrote:

Glad I could help. If you need more help on this later or need to customize
it some more, either add to this discussion (the system will send me
notification) or contact me direct at (remove spaces) HelpFrom @
jlathamsite.com

Enjoy.

"dribler2" wrote:

its really a holiday season
thanks for the gift, maybe someday i can give one too - i hope so.
Ill post another thread after thorough incorporation of your module to the
task i will prepare..

more power
dribler2

"JLatham" wrote:

Here is a formula that will show the "observe on" date for any give
fixed-date holiday, like New Year's or Air Force Day. I'll use Christmas
since it is easy to determine which is the day and which is the month part of
the date. If you need to reverse them, it'll be easy to figure that out:
=IF(WEEKDAY(DATEVALUE("12/25/" & A18))=1,DATEVALUE("12/25/" &
A18)+1,IF(WEEKDAY(DATEVALUE("12/25/" & A18))=7,DATEVALUE("12/25/" &
A18)-1,DATEVALUE("12/25/" & A18)))

In A18 I had the year, as 2007. So by changing the year in A18, you can
check the holidays for any given year. If you use Independence Day 2007
("9/15") you'll find that it will say to observe it on 9/14 (Friday) since
the 15th this year is on a Saturday.

Holy Thursday and Holy Friday are different animals, since they are based on
Easter, and Easter is definitely a variable date.

Here is a Function you can put into a code module and use as a UDF (user
defined function) to determine when Easter is, and based on that, come up
with the two Holy days in question:

Function DetermineEasterSunday(anyYear As Variant) As Variant
Dim anyYearValue As Variant
Dim myYear As Integer
Dim Century As Integer
Dim GoldenNumber As Integer
Dim GregorianFix As Integer
Dim ClavianFix As Integer
Dim Epact As Integer
Dim FindSundays As Integer
Dim DaysIntoMarch As Integer

anyYearValue = Val(anyYear)
If Int(anyYearValue) < anyYearValue Then
DetermineEasterSunday = "Invalid Year"
Exit Function
End If
If anyYearValue < 1583 Then
DetermineEasterSunday = "Invalid Year"
Exit Function
End If
myYear = Int(anyYearValue)
Century = Int(myYear / 100) + 1
GregorianFix = Int(Int(3 * Century) / 4) - 12
GoldenNumber = Int(myYear Mod 19) + 1
ClavianFix = Int(Int(8 * Century + 5) / 25) - 5 - GregorianFix
FindSundays = Int(Int(5 * myYear) / 4) - GregorianFix - 10
Epact = Int((Int(11 * GoldenNumber) + 20 + ClavianFix)) Mod 30
If Epact < 25 Then
If GoldenNumber 11 Then
Epact = Epact + 1
End If
End If
If Epact = 24 Then
Epact = Epact + 1
End If
DaysIntoMarch = 44 - Epact
If DaysIntoMarch <= 20 Then
DaysIntoMarch = DaysIntoMarch + 30
End If
DaysIntoMarch = (DaysIntoMarch + 7) - ((DaysIntoMarch + FindSundays) Mod
7)
DetermineEasterSunday = DateSerial(myYear, 3, 1 - 1 + DaysIntoMarch)
End Function

To use that, again presuming that the year is in A18, you would put this
equation into a cell to find Easter:
=DetermineEasterSunday(A18)

For Holy Friday use:
=DetermineEasterSunday(A18)-2

and for Holy Thursday use:
=DetermineEasterSunday(A18)-3

and format those cells as dates.

You said you had your long list of dates in column A as text. Need to see
what those entries look like on screen so we can set up some IF testing in a
column next to them to determine if they are holidays/observation days and
flag them accordingly.

I've also uploaded a workbook that has 2 sheets - first sheet shows the
processing in step by step detail, the 2nd sheet has the "all in one"
formulas for calculating these holidays and more, plus it has the function
code in it already. It is at
http://www.jlathamsite.com/uploads/N...anHolidays.xls


"dribler2" wrote:

thanks for the guide list,

here is the official non-working holidays for both public and private sector -
01 JAN
HOLY THURSDAY
HOLY FRIDAY
01 MAY
25 JUL
15 SEP
25 DEC

you are right about christian holiday like holy thursday and holy friday,
the dates varies.

happy holidays
dribler2

"JLatham" wrote:

Here's a list I managed to put together. If there are additions, deletions
or if I got something wrong, let me know (let me know if I got them correct,
also):

New Year's Day 1-Jan
Air Force Day 1-Feb
Ash Wednesday (start of Lent) 7 Wednesdays prior to Easter
Holy Thursday Thursday before Easter
Holy Friday Friday Before Easter
Easter varies
Labor Day 1-May
Army Day 27-May
Fiesta Day 1-Aug
San Jacinto Day 14-Sep
Independence Day 15-Sep
All Souls Day 2-Nov
Immaculate Conception 8-Dec
Christmas 25-Dec