First friday calculation

[arihazra]

Hi,

I tried to search but did not find any post regarding the requirements i have

a) calcuting the date of first Friday of a year, my input is year value.

b) calculating the date of 'Good friday' of a year, my input is year value.

Could anybody help.

Much appreciated for an early response

Thanks & regards
Arindam

 

[Mark W.]

a) =DATE(A1,1,1)+CHOOSE(WEEKDAY(DATE(A1,1,1),2),4,3,2,1,0,6,5) where A1 contains a year number.

b) It depends on when Easter falls. How do you intend to determine that?

 

[texasalynn]

Welcome to the Board!

here is the first one

=IF(WEEKDAY(A1,1)<>6,(6-WEEKDAY(A1,1))+A1,A1)
Where A1 will have the date - the weekday requires that the Analysis Toolpak be added in. To turn it on go to Tools --> Add-in --> check the box for Analysis Toolpak.

HTH

 

[Mark W.]

BTW, Easter is the first Sunday following the full moon that occurs on or next after the vernal equinox.

Excel doesn't have built-in functions for EQUINOX() or MOON().

 

[Mark W.]

...the weekday requires that the Analysis Toolpak be added in. To turn it on go to Tools --> Add-in --> check the box for Analysis Toolpak.

HTH

WEEKDAY is a native Excel function.

 

[Drew]

Pulled from Walkenbach orginally submitted by Thomas Jansen

To calculate Easter:
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

Just substract 2 at the end for Good Friday:
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6-2

where A1 houses the year.

As Walkebach says... "Please don't ask me to explain this formula. I haven't a clue."

 

[Mark W.]

Pulled from Walkenbach orginally submitted by Thomas Jansen

To calculate Easter:
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

I spot checked this for a few years. Seems to work. Pretty clever.

 

[Scott Huish]

First Friday of the year could be done as:

=CEILING(DATE(A1,1,1)-6,7)+6

where A1 contains the year.

 

[mortgageman]

Pulled from Walkenbach orginally submitted by Thomas Jansen

To calculate Easter:
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

Just substract 2 at the end for Good Friday:
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6-2

where A1 houses the year.

As Walkebach says... "Please don't ask me to explain this formula. I haven't a clue."

Not being a Christian :wink: , could someone explain the 14%

Gene, "The Mortgage Man", Klein

 

[barry houdini]

For first Friday of the year, a formula which works whichever date system you're using......

=DATE(A1,1,8)-WEEKDAY(DATE(A1,1,2))