Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #3  
Old 11-11-2022, 01:00 AM
ArviLaanemets ArviLaanemets is offline Easter Holiday formulas -- How do they do it!? [low priority post] Windows 8 Easter Holiday formulas -- How do they do it!? [low priority post] Office 2016
Expert
 
Join Date: May 2017
Posts: 961
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

I use formulas:
Code:
Easter Sunday:
=ROUND((DAY(MINUTE(YearNo/38)/2+55)&".4."& YearNo)/7,)*7-6
Easter Friday:
=ROUND((DAY(MINUTE(YearNo/38)/2+55)&".4."& YearNo)/7,)*7-6-2
First Pentecost:
=ROUND((DAY(MINUTE(YearNo/38)/2+55)&".4."& YearNo)/7,)*7-6+49
etc.
Got them from some german Excel newsgroup posting some 15+ years ago

I ported this formula into SQL Server (as an UDF) too, but there another one is working somewhat better (i.e. it is slightly faster):
Code:
USE [DBName]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_Easter] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_Easter] (@Year INT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @Date DATETIME
    DECLARE @c INT
    DECLARE @n INT
    DECLARE @i INT
    DECLARE @k INT
    DECLARE @j INT
    DECLARE @l INT
    DECLARE @m INT
    DECLARE @d INT
    SET @n = @Year - 19 * (@Year / 19)
    SET @c = @Year / 100
    SET @k = (@c - 17) / 25
    SET @i = @c - @c / 4 - (@c - @k) / 3 + 19 * @n + 15
    SET @i = @i - 30 * (@i / 30)
    SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) 
/ 11))
    SET @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4
    SET @j = @j - 7 * (@j / 7)
    SET @l = @i - @j
    SET @m = 3 + (@l + 40) / 44
    SET @d = @l + 28 - 31 * (@m / 4)
    SET @Date = CAST(@Year AS VARCHAR) + '-' + CAST(@m AS VARCHAR) + '-' + 
CAST(@d AS VARCHAR)
    RETURN @Date
END
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Easter Holiday formulas -- How do they do it!? [low priority post] Change template priority via VBA heathkat Word VBA 2 05-28-2018 08:15 PM
Easter Holiday formulas -- How do they do it!? [low priority post] Adding national holiday to Master File BarryB Project 3 04-19-2016 07:43 AM
Microsoft Project holiday integration jeccles123 Project 4 03-22-2015 01:42 PM
Easter Holiday formulas -- How do they do it!? [low priority post] MS Project Holiday pmjunkie Project 3 12-14-2011 09:08 AM
Calender month view peeve - display high priority over low priority appts? ghumdinger Outlook 0 07-25-2011 03:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft