| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
U.S. Holidays that the date moves
Are there Excel (not VB or VBA) formulas already posted that will give the
dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
Jim
Chip Pearson has done it. http://www.cpearson.com/excel/holidays.htm Mike "top.jimmy" wrote: Are there Excel (not VB or VBA) formulas already posted that will give the dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
Look he
http://www.cpearson.com/excel/holidays.htm#Easter -- Kind regards, Niek Otten Microsoft MVP - Excel "top.jimmy" wrote in message ... | Are there Excel (not VB or VBA) formulas already posted that will give the | dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every | year? A perpetual formula for each so to speak. | New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. | are easy. They never change. | | Anyone who has gone down this road and would like to share their efforts, I | would appreciate it!!! | JIM |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
What am I missing here to calculate Thanksgiving Day which is the 4th
Thursday of the month of November? How do I write DoW?: =DATE(YEAR(NOW()),11,1+((4th-(DoW=WEEKDAY(DATE(YEAR(NOW()),11,1))))*7)+(DoW-WEEKDAY(DATE(YEAR(NOW()),11,1)))) "Mike H" wrote: Jim Chip Pearson has done it. http://www.cpearson.com/excel/holidays.htm Mike "top.jimmy" wrote: Are there Excel (not VB or VBA) formulas already posted that will give the dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
Day of the week
If you check your other post I posted formulas for Easter, Memorial, Labor and Thanksgiving -- Regards, Peo Sjoblom "top.jimmy" wrote in message ... What am I missing here to calculate Thanksgiving Day which is the 4th Thursday of the month of November? How do I write DoW?: =DATE(YEAR(NOW()),11,1+((4th-(DoW=WEEKDAY(DATE(YEAR(NOW()),11,1))))*7)+(DoW-WEEKDAY(DATE(YEAR(NOW()),11,1)))) "Mike H" wrote: Jim Chip Pearson has done it. http://www.cpearson.com/excel/holidays.htm Mike "top.jimmy" wrote: Are there Excel (not VB or VBA) formulas already posted that will give the dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
For thanksgiving put the year in A1 and this formula in a cell
=DATE(A1,11,1+((4-(5=WEEKDAY(DATE(A1,11,1))))*7)+(5-WEEKDAY(DATE(A1,11,1)))) Mike "top.jimmy" wrote: What am I missing here to calculate Thanksgiving Day which is the 4th Thursday of the month of November? How do I write DoW?: =DATE(YEAR(NOW()),11,1+((4th-(DoW=WEEKDAY(DATE(YEAR(NOW()),11,1))))*7)+(DoW-WEEKDAY(DATE(YEAR(NOW()),11,1)))) "Mike H" wrote: Jim Chip Pearson has done it. http://www.cpearson.com/excel/holidays.htm Mike "top.jimmy" wrote: Are there Excel (not VB or VBA) formulas already posted that will give the dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
Here's my post again
Easter day =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 (by Tomas Jansen), format as date for US Memorial day =DATE(A1,5,31)-WEEKDAY(DATE(A1,5,31)-2) for US Labor Day =DATE(A1,9,8)-WEEKDAY(DATE(A1,9,6)) for US Thanksgiving =DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3)) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Day of the week If you check your other post I posted formulas for Easter, Memorial, Labor and Thanksgiving -- Regards, Peo Sjoblom "top.jimmy" wrote in message ... What am I missing here to calculate Thanksgiving Day which is the 4th Thursday of the month of November? How do I write DoW?: =DATE(YEAR(NOW()),11,1+((4th-(DoW=WEEKDAY(DATE(YEAR(NOW()),11,1))))*7)+(DoW-WEEKDAY(DATE(YEAR(NOW()),11,1)))) "Mike H" wrote: Jim Chip Pearson has done it. http://www.cpearson.com/excel/holidays.htm Mike "top.jimmy" wrote: Are there Excel (not VB or VBA) formulas already posted that will give the dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
SWEET!!! I'm assuming A1 contains the year, right?
"Peo Sjoblom" wrote: Here's my post again Easter day =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 (by Tomas Jansen), format as date for US Memorial day =DATE(A1,5,31)-WEEKDAY(DATE(A1,5,31)-2) for US Labor Day =DATE(A1,9,8)-WEEKDAY(DATE(A1,9,6)) for US Thanksgiving =DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3)) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Day of the week If you check your other post I posted formulas for Easter, Memorial, Labor and Thanksgiving -- Regards, Peo Sjoblom "top.jimmy" wrote in message ... What am I missing here to calculate Thanksgiving Day which is the 4th Thursday of the month of November? How do I write DoW?: =DATE(YEAR(NOW()),11,1+((4th-(DoW=WEEKDAY(DATE(YEAR(NOW()),11,1))))*7)+(DoW-WEEKDAY(DATE(YEAR(NOW()),11,1)))) "Mike H" wrote: Jim Chip Pearson has done it. http://www.cpearson.com/excel/holidays.htm Mike "top.jimmy" wrote: Are there Excel (not VB or VBA) formulas already posted that will give the dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
Correct
-- Regards, Peo Sjoblom "top.jimmy" wrote in message ... SWEET!!! I'm assuming A1 contains the year, right? "Peo Sjoblom" wrote: Here's my post again Easter day =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 (by Tomas Jansen), format as date for US Memorial day =DATE(A1,5,31)-WEEKDAY(DATE(A1,5,31)-2) for US Labor Day =DATE(A1,9,8)-WEEKDAY(DATE(A1,9,6)) for US Thanksgiving =DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3)) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Day of the week If you check your other post I posted formulas for Easter, Memorial, Labor and Thanksgiving -- Regards, Peo Sjoblom "top.jimmy" wrote in message ... What am I missing here to calculate Thanksgiving Day which is the 4th Thursday of the month of November? How do I write DoW?: =DATE(YEAR(NOW()),11,1+((4th-(DoW=WEEKDAY(DATE(YEAR(NOW()),11,1))))*7)+(DoW-WEEKDAY(DATE(YEAR(NOW()),11,1)))) "Mike H" wrote: Jim Chip Pearson has done it. http://www.cpearson.com/excel/holidays.htm Mike "top.jimmy" wrote: Are there Excel (not VB or VBA) formulas already posted that will give the dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
How many posts are there?
I posted formulas for Labor Day and Memorial day at one of his threads in ..excel. -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... Day of the week If you check your other post I posted formulas for Easter, Memorial, Labor and Thanksgiving -- Regards, Peo Sjoblom "top.jimmy" wrote in message ... What am I missing here to calculate Thanksgiving Day which is the 4th Thursday of the month of November? How do I write DoW?: =DATE(YEAR(NOW()),11,1+((4th-(DoW=WEEKDAY(DATE(YEAR(NOW()),11,1))))*7)+(DoW-WEEKDAY(DATE(YEAR(NOW()),11,1)))) "Mike H" wrote: Jim Chip Pearson has done it. http://www.cpearson.com/excel/holidays.htm Mike "top.jimmy" wrote: Are there Excel (not VB or VBA) formulas already posted that will give the dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
The one I posted to was one of those strange things where it seems he posted
to an old post from 2006 which you btw took part in (I noticed that after I posted my answer). http://tinyurl.com/2gfale if you scroll to the top you will find yourself in that thread from Nov 2006 <g Peo "T. Valko" wrote in message ... How many posts are there? I posted formulas for Labor Day and Memorial day at one of his threads in .excel. -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... Day of the week If you check your other post I posted formulas for Easter, Memorial, Labor and Thanksgiving -- Regards, Peo Sjoblom "top.jimmy" wrote in message ... What am I missing here to calculate Thanksgiving Day which is the 4th Thursday of the month of November? How do I write DoW?: =DATE(YEAR(NOW()),11,1+((4th-(DoW=WEEKDAY(DATE(YEAR(NOW()),11,1))))*7)+(DoW-WEEKDAY(DATE(YEAR(NOW()),11,1)))) "Mike H" wrote: Jim Chip Pearson has done it. http://www.cpearson.com/excel/holidays.htm Mike "top.jimmy" wrote: Are there Excel (not VB or VBA) formulas already posted that will give the dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
I see!
Now I'm in all 3 of his threads! -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... The one I posted to was one of those strange things where it seems he posted to an old post from 2006 which you btw took part in (I noticed that after I posted my answer). http://tinyurl.com/2gfale if you scroll to the top you will find yourself in that thread from Nov 2006 <g Peo "T. Valko" wrote in message ... How many posts are there? I posted formulas for Labor Day and Memorial day at one of his threads in .excel. -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... Day of the week If you check your other post I posted formulas for Easter, Memorial, Labor and Thanksgiving -- Regards, Peo Sjoblom "top.jimmy" wrote in message ... What am I missing here to calculate Thanksgiving Day which is the 4th Thursday of the month of November? How do I write DoW?: =DATE(YEAR(NOW()),11,1+((4th-(DoW=WEEKDAY(DATE(YEAR(NOW()),11,1))))*7)+(DoW-WEEKDAY(DATE(YEAR(NOW()),11,1)))) "Mike H" wrote: Jim Chip Pearson has done it. http://www.cpearson.com/excel/holidays.htm Mike "top.jimmy" wrote: Are there Excel (not VB or VBA) formulas already posted that will give the dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
I never understood this. This was an entry for the shortest formula
to return the correct Easter date, right? Why use the DOLLAR() function? ROUND() works as well from what I can tell, and it's one character shorter. Is this just an obfuscation technique? On Aug 30, 11:52 am, "Peo Sjoblom" wrote: Here's my post again Easter day =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 (by Tomas Jansen), format as date |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
I don't know why, regardless it didn't win. The winning formula by Norbert
Hetterich was =FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34 (converted to US) -- Regards, Peo Sjoblom "ilia" wrote in message ups.com... I never understood this. This was an entry for the shortest formula to return the correct Easter date, right? Why use the DOLLAR() function? ROUND() works as well from what I can tell, and it's one character shorter. Is this just an obfuscation technique? On Aug 30, 11:52 am, "Peo Sjoblom" wrote: Here's my post again Easter day =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 (by Tomas Jansen), format as date |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
U.S. Holidays that the date moves
Note that this was a German competition and the DOLLAR function in German is
DM (I assume it stands for Deutsche Mark) and ROUND is RUNDEN so the difference is 4 characters, so there you might have your explanation -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... I don't know why, regardless it didn't win. The winning formula by Norbert Hetterich was =FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34 (converted to US) -- Regards, Peo Sjoblom "ilia" wrote in message ups.com... I never understood this. This was an entry for the shortest formula to return the correct Easter date, right? Why use the DOLLAR() function? ROUND() works as well from what I can tell, and it's one character shorter. Is this just an obfuscation technique? On Aug 30, 11:52 am, "Peo Sjoblom" wrote: Here's my post again Easter day =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 (by Tomas Jansen), format as date |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Memorial day in Excel
this one works. all in Excel
=DATE(A4,5,1+(((IF(WEEKDAY(DATE(A4,5,1))=2,IF(WEE KDAY(DATE(A4,5,1))=7,5,4),5))-(2=WEEKDAY(DATE(A4,5,1))))*7)+(2-WEEKDAY(DATE(A4,5,1)))) This one returns the Memorial day when the year is in A4 Posted as a reply to: U.S. Holidays that the date moves Are there Excel (not VB or VBA) formulas already posted that will give the dates of Easter Sunday, Memorial Day, Labor Day, and Thanksgiving Day every year? A perpetual formula for each so to speak. New Years, Christmas, Valentine's, St. Patrick's, & Independence Days, etc. are easy. They never change. Anyone who has gone down this road and would like to share their efforts, I would appreciate it!!! JIM EggHeadCafe - Software Developer Portal of Choice WCF Workflow Services Using External Data Exchange http://www.eggheadcafe.com/tutorials...vices-usi.aspx |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
incorrect
Your Memorial day does not work correctly. Memorial day is always on the last Monday in May. On occasion it is on the 5th Monday in May. See my formula below.
=DATE(A3,5,1+(((IF(WEEKDAY(DATE(A3,5,1))=2,IF(WEE KDAY(DATE(A3,5,1))=7,5,4),5))-(2=WEEKDAY(DATE(A3,5,1))))*7)+(2-WEEKDAY(DATE(A3,5,1)))) this one will work correctly. Posted as a reply to: U.S. Holidays that the date moves Here's my post again Easter day =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 (by Tomas Jansen), format as date for US Memorial day =DATE(A1,5,31)-WEEKDAY(DATE(A1,5,31)-2) for US Labor Day =DATE(A1,9,8)-WEEKDAY(DATE(A1,9,6)) for US Thanksgiving =DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3)) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... EggHeadCafe - Software Developer Portal of Choice WCF Workflow Services Using External Data Exchange http://www.eggheadcafe.com/tutorials...vices-usi.aspx |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
incorrect
Neither of those formulas for Memorial Day (last Monday in May) work
correctly. A - get the date for the 1st Monday in June B - subtract 7 =DATE(year,6,8)-WEEKDAY(DATE(year,6,6))-7 -- Biff Microsoft Excel MVP <C Gray wrote in message ... Your Memorial day does not work correctly. Memorial day is always on the last Monday in May. On occasion it is on the 5th Monday in May. See my formula below. =DATE(A3,5,1+(((IF(WEEKDAY(DATE(A3,5,1))=2,IF(WEE KDAY(DATE(A3,5,1))=7,5,4),5))-(2=WEEKDAY(DATE(A3,5,1))))*7)+(2-WEEKDAY(DATE(A3,5,1)))) this one will work correctly. Posted as a reply to: U.S. Holidays that the date moves Here's my post again Easter day =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 (by Tomas Jansen), format as date for US Memorial day =DATE(A1,5,31)-WEEKDAY(DATE(A1,5,31)-2) for US Labor Day =DATE(A1,9,8)-WEEKDAY(DATE(A1,9,6)) for US Thanksgiving =DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3)) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... EggHeadCafe - Software Developer Portal of Choice WCF Workflow Services Using External Data Exchange http://www.eggheadcafe.com/tutorials...vices-usi.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for adding days to a date excluding weekends and holidays? | Excel Discussion (Misc queries) | |||
| Date/Time Difference Excluding Weekends & Holidays | Excel Worksheet Functions | |||
| Calculate A Date Excluding Weekends And Holidays | New Users to Excel | |||
| Calculate A Date Excluding Weekends And Holidays | New Users to Excel | |||
| Holidays | Excel Worksheet Functions | |||
Linear Mode
