i'm short for long formulas, can anyone help me with this.

assuming i have a 20 dates in text format (e.g. "25 DEC", "01 JAN", "01
MAY", etc..) in column X which contains the standard public holidays.
(x1:x20). i made it to 20 yet some cells are blank for any future additions)

and in column A, i have a series of dates for say 6 years, starting from
01/01/2006 down to 12/31/2012

Then, I need to collect the real dates of standard public holidays in column
A (e.g. 01/01/2006,01/01/2007,etc) and place them all in Column C, under a
defined name "holidays"..

surely someone knew this a since long time ago.

any suggestion is welcome.

driller

I'm going to toss a couple of questions at you before going further.

#1 - define "standard holiday". For what country (USA, I presume, but it's
a world-wide community here)? There are currently 10 recognized U.S. Federal
holidays, and then there are a variety of State holidays - thus my request to
define "standard holiday".

#2 - Do you want the actual date of the holiday or the day it is observed
on? For the Federal holidays in the U.S. the rule is if the holiday falls on
a Saturday, it is observed on the Friday before; if it falls on a Sunday,
then it is observed on the Monday after.

Remember unless you go look them up somewhere, some of the holidays fall on
different dates each year - Thanksgiving in the U.S. being one of those. The
actual day of some like that one is determined as some day of the week after
some other point in time.

#3 - looking for religious holidays also? Some of those are fun. Easter is
fun to compute.

"dribler2" wrote:

i'm short for long formulas, can anyone help me with this.

assuming i have a 20 dates in text format (e.g. "25 DEC", "01 JAN", "01
MAY", etc..) in column X which contains the standard public holidays.
(x1:x20). i made it to 20 yet some cells are blank for any future additions)

and in column A, i have a series of dates for say 6 years, starting from
01/01/2006 down to 12/31/2012

Then, I need to collect the real dates of standard public holidays in column
A (e.g. 01/01/2006,01/01/2007,etc) and place them all in Column C, under a
defined name "holidays"..

surely someone knew this a since long time ago.

any suggestion is welcome.

driller

Jlatham
Thanks for your interesting questions
#1. Country. Nicaragua. But i hope we can be availed to adjust the standard
holiday dates.
#2. Same shifting of holi-days, as you mentioned.
#3. Catholic Holidays for Easter included as standard holiday.

hope you can solve mine problem...happy holidays
dribler2

"JLatham" wrote:

I'm going to toss a couple of questions at you before going further.

#1 - define "standard holiday". For what country (USA, I presume, but it's
a world-wide community here)? There are currently 10 recognized U.S. Federal
holidays, and then there are a variety of State holidays - thus my request to
define "standard holiday".

#2 - Do you want the actual date of the holiday or the day it is observed
on? For the Federal holidays in the U.S. the rule is if the holiday falls on
a Saturday, it is observed on the Friday before; if it falls on a Sunday,
then it is observed on the Monday after.

Remember unless you go look them up somewhere, some of the holidays fall on
different dates each year - Thanksgiving in the U.S. being one of those. The
actual day of some like that one is determined as some day of the week after
some other point in time.

#3 - looking for religious holidays also? Some of those are fun. Easter is
fun to compute.

"dribler2" wrote:

i'm short for long formulas, can anyone help me with this.

assuming i have a 20 dates in text format (e.g. "25 DEC", "01 JAN", "01
MAY", etc..) in column X which contains the standard public holidays.
(x1:x20). i made it to 20 yet some cells are blank for any future additions)

and in column A, i have a series of dates for say 6 years, starting from
01/01/2006 down to 12/31/2012

Then, I need to collect the real dates of standard public holidays in column
A (e.g. 01/01/2006,01/01/2007,etc) and place them all in Column C, under a
defined name "holidays"..

surely someone knew this a since long time ago.

any suggestion is welcome.

driller

If you can provide dates/rules for determining the holidays (and their names)
in a format kind of like this one given for U.S. Fed holidays:
http://aa.usno.navy.mil/faq/docs/holidays.html


Then we can probably do business <g. The 'fixed date' holidays such as
Christmas and New Years are relatively easy to do, it's the "the first Sunday
after the full moon that occurs on or after the vernal equinox" ones (that's
Easter Sunday, by the way) are tougher.

If the list is short and doesn't need too much back-and-forth bantering, you
could post it here. Otherwise you could send the list to
HelpFrom @ jlathamsite.com (no spaces)
and I'll look into it and can probably have it done in time for Lent.

Now, I'm not up on all the good Catholic holidays, so you'd need to be
specific with regards to which ones you need and probably a hint as to how
they may relate to the primary holiday. Example: Lent and Good Friday are
based on Easter. So I'd need to know #days before Easter that Lent starts,
and (in theory) same for Good Friday.

I've done the standard U.S. holidays for our company's shared calendar, so I
generally have a process to determine such things as 1st or 3rd Monday in a
given month. It's just a matter of implementing the rules in code and
setting it up to be used as a User Defined Function for you to plug into your
workbook.

"dribler2" wrote:

Jlatham
Thanks for your interesting questions
#1. Country. Nicaragua. But i hope we can be availed to adjust the standard
holiday dates.
#2. Same shifting of holi-days, as you mentioned.
#3. Catholic Holidays for Easter included as standard holiday.

hope you can solve mine problem...happy holidays
dribler2

"JLatham" wrote:

I'm going to toss a couple of questions at you before going further.

#1 - define "standard holiday". For what country (USA, I presume, but it's
a world-wide community here)? There are currently 10 recognized U.S. Federal
holidays, and then there are a variety of State holidays - thus my request to
define "standard holiday".

#2 - Do you want the actual date of the holiday or the day it is observed
on? For the Federal holidays in the U.S. the rule is if the holiday falls on
a Saturday, it is observed on the Friday before; if it falls on a Sunday,
then it is observed on the Monday after.

Remember unless you go look them up somewhere, some of the holidays fall on
different dates each year - Thanksgiving in the U.S. being one of those. The
actual day of some like that one is determined as some day of the week after
some other point in time.

#3 - looking for religious holidays also? Some of those are fun. Easter is
fun to compute.

"dribler2" wrote:

i'm short for long formulas, can anyone help me with this.

assuming i have a 20 dates in text format (e.g. "25 DEC", "01 JAN", "01
MAY", etc..) in column X which contains the standard public holidays.
(x1:x20). i made it to 20 yet some cells are blank for any future additions)

and in column A, i have a series of dates for say 6 years, starting from
01/01/2006 down to 12/31/2012

Then, I need to collect the real dates of standard public holidays in column
A (e.g. 01/01/2006,01/01/2007,etc) and place them all in Column C, under a
defined name "holidays"..

surely someone knew this a since long time ago.

any suggestion is welcome.

driller

thanks JLatham,
i will gather your requirement and be in touch very soon.

happy holidays
driller

"JLatham" wrote:

If you can provide dates/rules for determining the holidays (and their names)
in a format kind of like this one given for U.S. Fed holidays:
http://aa.usno.navy.mil/faq/docs/holidays.html


Then we can probably do business <g. The 'fixed date' holidays such as
Christmas and New Years are relatively easy to do, it's the "the first Sunday
after the full moon that occurs on or after the vernal equinox" ones (that's
Easter Sunday, by the way) are tougher.

If the list is short and doesn't need too much back-and-forth bantering, you
could post it here. Otherwise you could send the list to
HelpFrom @ jlathamsite.com (no spaces)
and I'll look into it and can probably have it done in time for Lent.

Now, I'm not up on all the good Catholic holidays, so you'd need to be
specific with regards to which ones you need and probably a hint as to how
they may relate to the primary holiday. Example: Lent and Good Friday are
based on Easter. So I'd need to know #days before Easter that Lent starts,
and (in theory) same for Good Friday.

I've done the standard U.S. holidays for our company's shared calendar, so I
generally have a process to determine such things as 1st or 3rd Monday in a
given month. It's just a matter of implementing the rules in code and
setting it up to be used as a User Defined Function for you to plug into your
workbook.

"dribler2" wrote:

Jlatham
Thanks for your interesting questions
#1. Country. Nicaragua. But i hope we can be availed to adjust the standard
holiday dates.
#2. Same shifting of holi-days, as you mentioned.
#3. Catholic Holidays for Easter included as standard holiday.

hope you can solve mine problem...happy holidays
dribler2

"JLatham" wrote:

I'm going to toss a couple of questions at you before going further.

#1 - define "standard holiday". For what country (USA, I presume, but it's
a world-wide community here)? There are currently 10 recognized U.S. Federal
holidays, and then there are a variety of State holidays - thus my request to
define "standard holiday".

#2 - Do you want the actual date of the holiday or the day it is observed
on? For the Federal holidays in the U.S. the rule is if the holiday falls on
a Saturday, it is observed on the Friday before; if it falls on a Sunday,
then it is observed on the Monday after.

Remember unless you go look them up somewhere, some of the holidays fall on
different dates each year - Thanksgiving in the U.S. being one of those. The
actual day of some like that one is determined as some day of the week after
some other point in time.

#3 - looking for religious holidays also? Some of those are fun. Easter is
fun to compute.

"dribler2" wrote:

i'm short for long formulas, can anyone help me with this.

assuming i have a 20 dates in text format (e.g. "25 DEC", "01 JAN", "01
MAY", etc..) in column X which contains the standard public holidays.
(x1:x20). i made it to 20 yet some cells are blank for any future additions)

and in column A, i have a series of dates for say 6 years, starting from
01/01/2006 down to 12/31/2012

Then, I need to collect the real dates of standard public holidays in column
A (e.g. 01/01/2006,01/01/2007,etc) and place them all in Column C, under a
defined name "holidays"..

surely someone knew this a since long time ago.

any suggestion is welcome.

driller

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


"dribler2" wrote:

thanks JLatham,
i will gather your requirement and be in touch very soon.

happy holidays
driller

"JLatham" wrote:

If you can provide dates/rules for determining the holidays (and their names)
in a format kind of like this one given for U.S. Fed holidays:
http://aa.usno.navy.mil/faq/docs/holidays.html


Then we can probably do business <g. The 'fixed date' holidays such as
Christmas and New Years are relatively easy to do, it's the "the first Sunday
after the full moon that occurs on or after the vernal equinox" ones (that's
Easter Sunday, by the way) are tougher.

If the list is short and doesn't need too much back-and-forth bantering, you
could post it here. Otherwise you could send the list to
HelpFrom @ jlathamsite.com (no spaces)
and I'll look into it and can probably have it done in time for Lent.

Now, I'm not up on all the good Catholic holidays, so you'd need to be
specific with regards to which ones you need and probably a hint as to how
they may relate to the primary holiday. Example: Lent and Good Friday are
based on Easter. So I'd need to know #days before Easter that Lent starts,
and (in theory) same for Good Friday.

I've done the standard U.S. holidays for our company's shared calendar, so I
generally have a process to determine such things as 1st or 3rd Monday in a
given month. It's just a matter of implementing the rules in code and
setting it up to be used as a User Defined Function for you to plug into your
workbook.

"dribler2" wrote:

Jlatham
Thanks for your interesting questions
#1. Country. Nicaragua. But i hope we can be availed to adjust the standard
holiday dates.
#2. Same shifting of holi-days, as you mentioned.
#3. Catholic Holidays for Easter included as standard holiday.

hope you can solve mine problem...happy holidays
dribler2

"JLatham" wrote:

I'm going to toss a couple of questions at you before going further.

#1 - define "standard holiday". For what country (USA, I presume, but it's
a world-wide community here)? There are currently 10 recognized U.S. Federal
holidays, and then there are a variety of State holidays - thus my request to
define "standard holiday".

#2 - Do you want the actual date of the holiday or the day it is observed
on? For the Federal holidays in the U.S. the rule is if the holiday falls on
a Saturday, it is observed on the Friday before; if it falls on a Sunday,
then it is observed on the Monday after.

Remember unless you go look them up somewhere, some of the holidays fall on
different dates each year - Thanksgiving in the U.S. being one of those. The
actual day of some like that one is determined as some day of the week after
some other point in time.

#3 - looking for religious holidays also? Some of those are fun. Easter is
fun to compute.

"dribler2" wrote:

i'm short for long formulas, can anyone help me with this.

assuming i have a 20 dates in text format (e.g. "25 DEC", "01 JAN", "01
MAY", etc..) in column X which contains the standard public holidays.
(x1:x20). i made it to 20 yet some cells are blank for any future additions)

and in column A, i have a series of dates for say 6 years, starting from
01/01/2006 down to 12/31/2012

Then, I need to collect the real dates of standard public holidays in column
A (e.g. 01/01/2006,01/01/2007,etc) and place them all in Column C, under a
defined name "holidays"..

surely someone knew this a since long time ago.

any suggestion is welcome.

driller

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

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

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

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

more power !!!

"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

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

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

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

more power,sir.
4pinoy

"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

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

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