Problem with Dates

Ralph M

Active Member
Joined
Apr 16, 2002
Messages
496
I have a WorkSheet with a schedule on it and I am working with dates and formulas as the dates will change every year (the user type the year in cell "C1"), I have a special event that occurs on Sunday after Christmas but before January 1st. If there is no Sunday before Jan 1st, then December 30th. Can someone help me with the formula?
Thanks
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

kkknie

Well-known Member
Joined
Apr 29, 2002
Messages
677
Not sure if this is what you want, but here goes...

=IF(WEEKDAY(Q14)>1,Q14+(8-WEEKDAY(Q14)),Q14+5)

Basically, if Christmas falls on any other day than Sunday, there will be another Sunday in December.
So if the day of the week is > 1 (Sunday) add 8 minus the day of the week to Christmas day.
Otherwise, just add 5 days to get to December 30th.

K
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Check the weekday function to test what day 12/25/2004 is and go from there.
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Ralph M said:
I have a WorkSheet with a schedule on it and I am working with dates and formulas as the dates will change every year (the user type the year in cell "C1"), I have a special event that occurs on Sunday after Christmas but before January 1st. If there is no Sunday before Jan 1st, then December 30th. Can someone help me with the formula?
Thanks

That would be December 26, 2004. What are you trying to do, besides flag the Sunday after Christmas? What formulas are you using now?
 

Ralph M

Active Member
Joined
Apr 16, 2002
Messages
496

ADVERTISEMENT

kkknie,
That's it, thanks so much.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
how about something like this?....
Book14
ABCD
1YearSundayBefore
2200312/28/2003
Sheet2


HTH

edit: wow! i was a little slow on that one (reminder to self - refresh before post :oops: )
 

Ralph M

Active Member
Joined
Apr 16, 2002
Messages
496

ADVERTISEMENT

Thanks to all, got it.

I have one more (I think). I need to find the 1st Sunday after January 6th. I've been trying to figure out the formula on my own, to no avail.
The year would be in cell "C1". Thanks for any help.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Book1
BCDE
1Date6/01/2004
2FollowingSunday:Sun11/01/2004
Sheet2


formula is:

=DATE(YEAR(C1),MONTH(C1),DAY(C1)+CHOOSE(WEEKDAY(C1),7,6,5,4,3,2,1))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top