Formula to get 1st,2nd,3rd,4th,5th Sundays from month end date.

Need cell formula or UDF that will return the 1st, 2nd, 3rd, 4th, or 5th
Sunday's dates in cells when the month end date is given in a cell.  For
example:

In cell P1, a date of 10/31/05 is entered. I need the following dates:

In cell F3 10/02/05 (the 1st Sunday in October)
In cell H3 10/09/05 (the 2nd Sunday in October)
In cell J3  10/16/05 (the 3rd Sunday in October)
In cell L3  10/23/05 (the 4th Sunday in October)
In cell N3  10/30/05 (the 5th Sunday in October)

Any formula suggestions would be greatly appreciated.  Mikeburg


-- 
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581
View this thread: http://www.excelforum.com/showthread.php?threadid=471744

0
9/29/2005 1:50:48 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
523 Views

Similar Articles

[PageSpeed] 11

F3=DATE(YEAR(P1),MONTH(P1),1)+(7-WEEKDAY(DATE(YEAR(P1),MONTH(P1),1),2))
F4=F3+7
F5=F4+7
F6=F5+7
F7=F6+7

Depending on the number of weeks, the formula in F7 may result in a November 
date.  That can be fixed as well.

"mikeburg" wrote:

> 
> Need cell formula or UDF that will return the 1st, 2nd, 3rd, 4th, or 5th
> Sunday's dates in cells when the month end date is given in a cell.  For
> example:
> 
> In cell P1, a date of 10/31/05 is entered. I need the following dates:
> 
> In cell F3 10/02/05 (the 1st Sunday in October)
> In cell H3 10/09/05 (the 2nd Sunday in October)
> In cell J3  10/16/05 (the 3rd Sunday in October)
> In cell L3  10/23/05 (the 4th Sunday in October)
> In cell N3  10/30/05 (the 5th Sunday in October)
> 
> Any formula suggestions would be greatly appreciated.  Mikeburg
> 
> 
> -- 
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581
> View this thread: http://www.excelforum.com/showthread.php?threadid=471744
> 
> 
0
9/29/2005 2:59:03 PM
One way:

This is based on

    http://cpearson.com/excel/DateTimeWS.htm#NthDoW

    =DATE(YEAR(A1),MONTH(A1),1+((N-(D>=WEEKDAY( 
DATE(YEAR(A1),MONTH(A1),1))))*7)+(D-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))))

Where N = nth (e.g., for F3, 1; H3, 2)
D = Day of the week (Sunday = 1)
and A1 contains the last (or any other) day of the month.



In article <mikeburg.1w4iuc_1128002705.7785@excelforum-nospam.com>,
 mikeburg <mikeburg.1w4iuc_1128002705.7785@excelforum-nospam.com> 
 wrote:

> Need cell formula or UDF that will return the 1st, 2nd, 3rd, 4th, or 5th
> Sunday's dates in cells when the month end date is given in a cell.  For
> example:
> 
> In cell P1, a date of 10/31/05 is entered. I need the following dates:
> 
> In cell F3 10/02/05 (the 1st Sunday in October)
> In cell H3 10/09/05 (the 2nd Sunday in October)
> In cell J3  10/16/05 (the 3rd Sunday in October)
> In cell L3  10/23/05 (the 4th Sunday in October)
> In cell N3  10/30/05 (the 5th Sunday in October)
> 
> Any formula suggestions would be greatly appreciated.  Mikeburg
0
jemcgimpsey (6723)
9/29/2005 3:04:08 PM
Works perfectly.  Used if statement on 5th Sunday to avoid going pas
month end date!

You guys are wonderful.  Would pay you a million dollars if I coul
afford it!  I am learning so very much from y'all.

Thanks again!

mikebur

--
mikebur
-----------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458
View this thread: http://www.excelforum.com/showthread.php?threadid=47174

0
9/29/2005 3:45:15 PM
On Thu, 29 Sep 2005 08:50:48 -0500, mikeburg
<mikeburg.1w4iuc_1128002705.7785@excelforum-nospam.com> wrote:

>
>Need cell formula or UDF that will return the 1st, 2nd, 3rd, 4th, or 5th
>Sunday's dates in cells when the month end date is given in a cell.  For
>example:
>
>In cell P1, a date of 10/31/05 is entered. I need the following dates:
>
>In cell F3 10/02/05 (the 1st Sunday in October)
>In cell H3 10/09/05 (the 2nd Sunday in October)
>In cell J3  10/16/05 (the 3rd Sunday in October)
>In cell L3  10/23/05 (the 4th Sunday in October)
>In cell N3  10/30/05 (the 5th Sunday in October)
>
>Any formula suggestions would be greatly appreciated.  Mikeburg

With any date in the month in P1, the first Monday is given by the formula:

=F3-DAY(F3)+8-WEEKDAY(F3-DAY(F3)+6).

So,

F3: =P1-DAY(P1)+8-WEEKDAY(P1-DAY(P1)+7)
H3: =F3+7
J3: =H3+7
I3: =J3+7
N3: =IF(MONTH(M3)=MONTH(M3+7),M3+7,"")

N3 will be blank if there are only four Sundays


--ron
0
ronrosenfeld (3122)
9/29/2005 5:40:02 PM
On Thu, 29 Sep 2005 13:40:02 -0400, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>=F3-DAY(F3)+8-WEEKDAY(F3-DAY(F3)+6).

That should read:

=P1-DAY(P1)+8-WEEKDAY(P1-DAY(P1)+7)
--ron
0
ronrosenfeld (3122)
9/29/2005 5:59:42 PM
Reply:

Similar Artilces:

error when you get to ohio st
how do I fix? It would make it much easier for us to understand the question, if you actually provided a full question with information we can work with. Give some details. "GWMOORE" wrote: > how do I fix? There's a hidden worksheet in that workbook. Window|unhide and unhide Validation The formula in Y2 should be: =Bracket!R40 The formula in Y3 should be: =Bracket!R42 The Formula in Z2 should be: =Bracket!R44 The Formula in Z2 should be: =Bracket!R46 Each column is the previous cell +4 rows (R40, R44, R48, ... or R42, R46, R50, ....) Then rehide the Validation ...

Shift to end of column
Can someone help with a relatively simple task. I have columns of data with specific headings. I have created names for each of the colums and now need to write a macro that will: 1. go to a required column 2. do a calculation (say average) of the data in that column and place the result 2 cells BELOW the last filled cell in that column Can someone smart out there help me with the code? Many thanks Hume In a roundabout sort of way the code below will do it. (Use it as a starter) Sub AverageColumn() Dim lColumnLetter As String Dim sTopOfRange As String, sBottomofRange As String Dim rRange...

help with formula please #2
Can anyone please help with this formula. If Sheet1 Column A = nothing(blank) And Column B = \\\\\\CC\\\\\\ Then Sheet 2 B7 = SumTotal. As a regular formula would be nice or VB code Hi Richard you want nothing at all in the whole of column A in sheet 1? do you want every cell in column B of sheet 1 to have \\\\\\CC\\\\\\ or should this (can this) only appear in one cell? and what range are you summing on sheet 2 in cell B7? Cheers JulieD "Richard" <anonymous@discussions.microsoft.com> wrote in message news:82ce01c477e1$77f0e740$a301280a@phx.gbl... > Can anyone please ...

Appending or Up-dating a formula
I am using an =Ave function in a formula to average quite a few cell values together. I add new information all the time; sometimes 5 or 6 new cells at a time. How can I append or up-date my formula to include these new cells with out have to type the new cells into the formula? When I make the cell that has the formula in it active/edit, all of the cells that are referenced in the formula have a highlight around them. Is there a key or key combination I can press while clicking on the cells I want to add to the formula? Thanks, john Hi see your post in Excel.misc -- Regards Frank ...

Changing a range of an array in a SUMPRODUCT formula gives a #N/A error
I have a working SUMPRODUCT formula {=SUMPRODUCT(($C$4:$C$4341=z8)*(audittypefm1="Standard"))} that I need to change the 1st array to C5623. When I do I get a #N/A error. I've tried giving the range a name (empnofm2), and I've verified all the data in the C4:C5623 range is formated the same (general, it is all employee numbers from 2 to 5 digits long. I'm using a "trim all" macro which is working great. I am unable to determine what is causing the #N/A and how to fix it. Basically I copied an entire worksheet within the same workbook and changed the name, wh...

Removing a date by inputting a newer one in another cell
I have office excel 2007. I am a fairly new user, I have a worksheet which maintains and monitors the movement of various units, and is controlled by quantity and dates. What I would like to do is write a formula that will remove the ordered date when I input the new start date, which would be the newer date. So far I have not been able to make this work. I would appreciate any help or advice you can give me. Many Thanks Mike You could use a worksheet_change event that clearscontents of the original cell. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com ...

help with a formula #5
I have 98 sets of magazines each set has 12 in the set what formula can I use to get a total of issues in all sets have you tried 98*12 -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Just Me" <no@isp.com> wrote in message news:eZ66ZEGHGHA.1192@TK2MSFTNGP11.phx.gbl... >I have 98 sets of magazines each set has 12 in the set > > what formula can I use to get a total of issues in all sets > =98*12 or =A1*A2 if the numbers are in A1 and A2 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Just Me" <n...

Can offset be used in this formula?
Can the offset be used in the below formula for the information in column "K"? In column L have the the following formula's L46 =MAX($I46*1000-$K$46*$J46,0) L47 =MAX($I47*1000-$K$46*$J47,0) .... L99 =MAX($I99*1000-$K$46*$J99,0) What I'd like to do is to copy the fomula into columns M, N, O, ... M47 =MAX($I47*1000-$K$47*$J47,0) M48 =MAX($I48*1000-$K$47*$J48,0) M49 =MAX($I49*1000-$K$47*$J49,0) .... N48 =MAX($I48*1000-$K$48*$J48,0) N49 =MAX($I49*1000-$K$48*$J49,0) .... O50 =MAX($I50*1000-$K$50*$J50,0) O51 =MAX($I51*1000-$K$50*$J51,0) .... How abou...

changing computers; how to get all of my emails/signatures over
We often build new computers at the office and now I am getting a new machine. How do I get all of my old emails and signatures onto my new computer? I will still be using outlook. Outlook & Exchange/Windows Messaging Backup and Dual-Boot: http://www.slipstick.com/config/backup.htm -- Nikki Peterson [MVP - Outlook] "ops man" <cs@gigagolf.com> wrote in message news:f26aa101.0311180604.98bd612@posting.google.com... We often build new computers at the office and now I am getting a new machine. How do I get all of my old emails and signatures onto my new computer? I wi...

Formula #29
Hi, What formula should I use if I want it to look at a specific cell and then work out the total number between two numbers. e.g. if cell H6 had 7-4 entered the number 9 would be returned. Thanks in advance I'm not sure how you got 9, but your life would be much easier if you used two separate cells and then subtracted the smaller from the larger (and then added one???). Boenerge wrote: > > Hi, > What formula should I use if I want it to look at a specific cell and then > work out the total number between two numbers. > e.g. if cell H6 had 7-4 entered the number 9 woul...

Problem getting values in iframe
Hi, I have placed an iframe to the account form. This iframe calls an .aspx page that is also placed on the same server as the CRM. I am trying to get an value from the CRM form to the .aspx page in the iFrame. my function that I call upon "onLoad": <script language="javascript"> function GetSomeValues() { document.getElementById('TextBox1').value = parent.document.forms[0].all.name.DataValue; } </script> This fails! I get the message: "Access denied!" Is there any setting in the CRM I have forgotten to turn on or off?? Can a...

MFC .exe gets access violation exception in release version but not debug
Hello, thanks for trying to help. I have an MFC .exe that gets an access violation (C0000005) exception in the release version but not the debug version. So I don't know what line of code causes it. But why should they act differently in this way at all? The exception occurs when a tab on a property sheet is being clicked. The only output in the debug screen when the tab is being clicked is "Warning: skipping non-radio button in group." Would that cause it? Does tab order determine groups along with the tab stop property? What are reasons why MFC code would get an access ...

Formula to return tomorrow's date.
I have a report that must be turned in the night before for tomorrow's date. Is ithere a date formula that will return tomorrow's date to a cell in Excel? One way: =TODAY() + 1 In article <668782AC-774A-475F-9821-8A4C3B14A983@microsoft.com>, "Shadyhosta" <Shadyhosta@discussions.microsoft.com> wrote: > I have a report that must be turned in the night before for tomorrow's date. > Is ithere a date formula that will return tomorrow's date to a cell in Excel? I assume that you want the date to remain static after entry. Easiest way, use 2...

To find out number of sundays and saturdays in january month
Hai all, How I can find out the number of saturdays and sundays in January For example in A1 and B1 cell represents Saturdays and Sundays. How I can find out the number of saturdays and sundays in that month and to write in corresponsing A2 and B2 column . Please help with thanks and regards Pol =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2008,1,1)&":"&DATE(2008,1,31))),1)=1)) Would tell you the number of Sundays in January of 2008. =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2008,1,1)&":"&DATE(2008,1,31))),1)=7)) Would tell you the number of Saturd...

Weekday formula
Can anyone help with the weekday formula. What I want to do is enter the date in say A1 11/20/2003 and have B return the answer of Thursday. Currently I use the weekday formula bu only returns the number of the day. Thanks in advance B ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com There are several ways to do this. One is to enter =A1 and format the cell with a custom number format of dddd. Another way is to use the function =TEXT(A1,"dddd"). -- Cordiall...

Formula to return a formula
At least I think that's what I'm looking for. here's my situation: I've got a sheet where there is a variable (X) that changes depending on what row it is on. So I have a column (column c) that lists these variables (X is dependant on things from a different sheet). X is a muliplier that is utilized differently depending on the value of N. N is a Picklist selection and can change periodically. Or even be duplicated on more than one row. I have a vlookup that checks the value of N (column a) and needs to return a formula that has X applied if needed on the approp...

Capturing Date
I have tried writing a macro that would let me capture the date from my computer and place it in cell D2. The captured day will be moved intact at the end of the day Is there a command that will let me do that? Thanks for the help in advance. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200609/1 Hi, Why do you need a macro for this if Excel has a formula like this? In Cell D2, put: =TODAY() "Larry Fish via OfficeKB.com" wrote: > I have tried writing a macro that would let me capture the date from my > computer and place it in...

Count formula within a named range.
Hi, How do I change the following formula =SUMIF($F$39:$F$79,"PW Shopfitters",$D$39:$D$79)/COUNTIF($F$39:$F$79,"PW Shopfitters") to count within a named range (PW Shopfitters). Any help would be great. Cheers, Phil Hi A range name cannot have spaces, maybe that's your problem. Name your range PW_Shopfitters. Then replace your cell references with PW_Shopfitters, eg =Sumif(PW_Shopfitters,"PW Shopfitters",............ -- j.kasselman@atlantic.net.remove_2nd_at. Randburg, Gauteng, South Africa "PW11111" wrote: > > Hi, > &g...

How to use a text formula as code formula
Hello. I have this formula loaded from a text field on a form: val([field10])+val([field20]) and I would like to use this as a code formula. I mean, if I use that in VBA code it works, but If I load from a text field it doesn't work. I need that my form use that formula stored in a text field to calculate values. Regards in advance, Marco responded to in another newsgroup. It's rarely necessary to post the same question to more than one group. If it is necessary, select all the relevant newsgroups in the "To:" or "Newsgroups:" field. That way, folks who...

Including a formula in a path name.
Dear MS, How about a new function for this one. I'm sure MANY users have the same problem as me. The research below seems quite comprehensive and has effectively come up against a brick wall. Thanks Peter Harlan Grove posted this UDF: http://www.google.com/groups?selm=hkQVb.2432%24_4.259%40www.newsranger.com Peter wrote: > > Very Helpful Papou - thank you! > Do you know of an additional / alternative method where you achieve the same tihing but are not required to have all the relevant work sheets open? > Many thanks > Peter > > "papou" wrote: > ...

FORMULA #14
What formula in access can do =COUNTIF(CAF!D:D,A3) Basicly I have 2 tables I want the 2nd table to count how many times a number is listed in the 1st table. I am trying to do this with a query and the count expression but keep getting errors. Dont bother with this one guys just figured it out AMDGUY [MCP] wrote: > What formula in access can do > =COUNTIF(CAF!D:D,A3) > > Basicly I have 2 tables > > I want the 2nd table to count how many times a number is listed in the > 1st table. I am trying to do this with a query and the count expression > but keep getting e...

What does the $ symbol mean in a formula?
What does the $ symbol mean in a formula? example: =G8*($A$14*2)*100 thanx The $ is used for absolut referencing. For e.g lets take your formula =G8*($A$14*2)*100 suppose you enter this formula in cell B2, now simply copy this formula one cell to the right (C2) by and one cell down (B3). C2: =H8*($A$14*2)*100 B3: =G9*($A$14*2)*100 So you see that The original G8 has changed, whereas $A$1 remains the same in both the new formulae because of the dollar signs. Mangesh "S" <S@discussions.microsoft.com> wrote in message news:D86597A4-C0A1-46AF-9DA6-FE06AB9C1FA0@mi...

open 2nd mailbox from a profile
I have 2 profiles, one for work (using Exchange) and one for personal. I was able to open my personal mailbox/calendar from the work profile by opening the local outlook data file. However, I have not been able to figure out how to view the work mailbox/calendar from my personal profile, since the work mailbox/ calendar is not in a local outlook data file. (I don't need to connect to the work mailbox/calendar, just to view it offline). Anyone know if this is possible? Thanks! It would not be available offline but you can always use OWa. -- Milly Staples [MVP - Outlook] Post all repli...

David A.
David posted this message in a group: I am back together all in one piece with magic glue, screws and a lot of specialist help from some wonderfully talented people. Going for a fall down a mountain side of seventy metres is not a good idea at night. Was only my helmet fire flying jacket and pants that saved me, but oh dear, there were some funny shaped bones and a lot of bark missing. Flying in the terrible fires at mid-evening doing my good community and civil duty, sending the data and pictures back to central fire control. Had a major engine failure on a near brand new out the box ...

The Quickest way to get Top Page On GooGle...
2 months I had first page organic results and they delivered on every key term I wanted so here they are http://www.atomicsearchengineoptimization.com or you can go straight to their contct page to get a great free report and a friendly call back. http://www.atomicsearchengineoptimization.com/Monthy_plan_rates-seo-services.html SPAM ...