Non-VBA formula to find 2nd Sunday of a given month

Can anyone help me write a formula to find the date of the second sunda
in a given month?

Thanks in Advance,

Dav

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/27/2003 10:12:53 PM
excel.misc 78881 articles. 5 followers. Follow

15 Replies
1666 Views

Similar Articles

[PageSpeed] 1

David,

Assuming you have a date in A1, this gives the 2nd Sunday of that date

=DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7


-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"DavidObeid" <DavidObeid.xkpiy@excelforum-nospam.com> wrote in message
news:DavidObeid.xkpiy@excelforum-nospam.com...
>
> Can anyone help me write a formula to find the date of the second sunday
> in a given month?
>
> Thanks in Advance,
>
> Dave
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
11/27/2003 10:48:07 PM
Hi David,

This appears to work and is one method,

=IF(WEEKDAY(DATE(YEAR(J8),MONTH(J8),1))=1, DATE(YEAR(J8),MONTH(J8),1)+7,
DATE(YEAR(J8),MONTH(J8),1)+15-WEEKDAY(DATE(YEAR(J8),MONTH(J8),1)))

J8 is where you have a date (say 27 Nov 2003).

You get an answer of 9 Nov 2003.

If you have J8 equal to 15 Feb 2004, you should get as an answer 8 Feb 2004.

Hope that helps.

Regards,
Kevin



"DavidObeid" <DavidObeid.xkpiy@excelforum-nospam.com> wrote in message
news:DavidObeid.xkpiy@excelforum-nospam.com...
>
> Can anyone help me write a formula to find the date of the second sunday
> in a given month?
>
> Thanks in Advance,
>
> Dave
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
stecyk (172)
11/27/2003 10:51:46 PM
Hi Bob,

I like your answer better. :-)

Regards,
Kevin


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23%23PwGiTtDHA.2360@TK2MSFTNGP09.phx.gbl...
> David,
>
> Assuming you have a date in A1, this gives the 2nd Sunday of that date
>
> =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7
>
>
> -- 
>
> HTH
>
> Bob Phillips
>     ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mailing direct)
>
> "DavidObeid" <DavidObeid.xkpiy@excelforum-nospam.com> wrote in message
> news:DavidObeid.xkpiy@excelforum-nospam.com...
> >
> > Can anyone help me write a formula to find the date of the second sunday
> > in a given month?
> >
> > Thanks in Advance,
> >
> > Dave
> >
> >
> > ------------------------------------------------
> > ~~ Message posted from http://www.ExcelTip.com/
> > ~~View and post usenet messages directly from http://www.ExcelForum.com/
> >
>
>


0
stecyk (172)
11/27/2003 10:54:53 PM
On Thu, 27 Nov 2003 16:12:53 -0600, DavidObeid
<DavidObeid.xkpiy@excelforum-nospam.com> wrote:

>
>Can anyone help me write a formula to find the date of the second sunday
>in a given month?
>
>Thanks in Advance,
>
>Dave
>
>
>------------------------------------------------
>~~ Message posted from http://www.ExcelTip.com/
>~~View and post usenet messages directly from http://www.ExcelForum.com/

I think I have it correct this time <g>.

If the first of the month is in A1, then:

 =A1+13-WEEKDAY(A1,3)

If it's a random date in the month in A1, then:

 =DATE(YEAR(A1),MONTH(A1),14)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),3)


--ron
0
ronrosenfeld (3122)
11/28/2003 2:33:02 AM
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote...
>Assuming you have a date in A1, this gives the 2nd Sunday of that date
>
>=DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7
....

I love 'name that tune' problems. Another alternative,

=A1-DAY(A1)-WEEKDAY(A1-DAY(A1))+15


0
hrlngrv (1990)
11/28/2003 6:49:06 AM
I believe this is just another option....

=EOMONTH(A1,-1)-WEEKDAY(EOMONTH(A1,-1))+15

-- 
Dana DeLouis
Using Windows XP  &  Office XP
= = = = = = = = = = = = = = = = =


"DavidObeid" <DavidObeid.xkpiy@excelforum-nospam.com> wrote in message
news:DavidObeid.xkpiy@excelforum-nospam.com...
>
> Can anyone help me write a formula to find the date of the second sunday
> in a given month?
>
> Thanks in Advance,
>
> Dave


0
delouis (422)
11/28/2003 7:10:07 AM
"Dana DeLouis" <delouis@bellsouth.net> wrote...
>I believe this is just another option....
>
>=EOMONTH(A1,-1)-WEEKDAY(EOMONTH(A1,-1))+15
....

But it does require the ATP.


0
hrlngrv (1990)
11/28/2003 7:31:49 AM
Harlan,

How would you adapt your formula for the second Monday (David's second
request)?

I tried simply increasing the 15 to 16, but I get the wrong answer when I
examine March 2004, using a "seed" (or A1) date of 15 March 2004.  I get an
answer of 15 March 2004 (same as the seed, coincidence), but the correct
answer should be 8 March 2004.

TIA.

Regards,
Kevin



> I love 'name that tune' problems. Another alternative,
>
> =A1-DAY(A1)-WEEKDAY(A1-DAY(A1))+15


0
stecyk (172)
11/28/2003 4:34:38 PM
How about

=A1-DAY(A1)-WEEKDAY(A1-DAY(A1),2)+15


Here are 2 other options (for 1900 date system)

=INT((DATE(YEAR(A1),MONTH(A1),5))/7)*7+9

for all date systems

=DATE(YEAR(A1),MONTH(A1),15)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6))



-- 

Regards,

Peo Sjoblom

"Kevin Stecyk" <stecyk@nohotspammail.com> wrote in message
news:OdYoo2ctDHA.1996@TK2MSFTNGP12.phx.gbl...
> Harlan,
>
> How would you adapt your formula for the second Monday (David's second
> request)?
>
> I tried simply increasing the 15 to 16, but I get the wrong answer when I
> examine March 2004, using a "seed" (or A1) date of 15 March 2004.  I get
an
> answer of 15 March 2004 (same as the seed, coincidence), but the correct
> answer should be 8 March 2004.
>
> TIA.
>
> Regards,
> Kevin
>
>
>
> > I love 'name that tune' problems. Another alternative,
> >
> > =A1-DAY(A1)-WEEKDAY(A1-DAY(A1))+15
>
>


0
terre081 (3244)
11/28/2003 5:35:54 PM
Peo,

Thank you very much!

Can I bother you further to inquire as to the logic.  I will tell you where
I got to, and then you can correct or add as the case might be?

Let's use 15 March 2004 as an example.

=A1-DAY(A1)-WEEKDAY(A1-DAY(A1),2)+15

A1-DAY(A1) gives the last day of the prior month. 29 Feb 2004.

WEEKDAY(A1-DAY(A1),2)...is the weekday of the last day of the prior month,
using Monday as 1.  So in our case, we have Sunday or 7.
Questions:
1) Why did you use the "2" parameter?  Guess: you wanted the Monday to be
one, just as for the Sunday example, Sunday was 1.
2) Would the symmetry of this example break down if you wanted the 2nd
Thursday because you cannot set Thursday to 1?

A1-DAY(A1)-WEEKDAY(A1-DAY(A1),2)

Yields the 22nd (=29-7) of February.  You are always going to "back-up" into
the previous month to a Sunday where you have a full week remaining in prior
month?  Now you add 15.


2nd Example:

=DATE(YEAR(A1),MONTH(A1),15)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6))

I understand the end result of the equation, though I do not understand why
you started off with the 15th of the current month.  So I am missing step 1.
I suspect once I get a slight push, your solution will rapidly make sense.
But right now, I am stumped.

The reason for asking my questions is to better my understanding.  Like most
everything else, a bit of practice always helps.

Thank you so much Peo.

Best regards,
Kevin


"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:eI5KTYdtDHA.1760@TK2MSFTNGP10.phx.gbl...
> How about
>
> =A1-DAY(A1)-WEEKDAY(A1-DAY(A1),2)+15
>
>
> Here are 2 other options (for 1900 date system)
>
> =INT((DATE(YEAR(A1),MONTH(A1),5))/7)*7+9
>
> for all date systems
>
> =DATE(YEAR(A1),MONTH(A1),15)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6))
>
>
>
> -- 
>
> Regards,
>
> Peo Sjoblom


0
stecyk (172)
11/28/2003 6:47:13 PM
Kevin,

>
> Thank you very much!
>
> Can I bother you further to inquire as to the logic.  I will tell you
where
> I got to, and then you can correct or add as the case might be?
>
> Let's use 15 March 2004 as an example.
>
> =A1-DAY(A1)-WEEKDAY(A1-DAY(A1),2)+15
>
> A1-DAY(A1) gives the last day of the prior month. 29 Feb 2004.
>
> WEEKDAY(A1-DAY(A1),2)...is the weekday of the last day of the prior month,
> using Monday as 1.  So in our case, we have Sunday or 7.
> Questions:
> 1) Why did you use the "2" parameter?  Guess: you wanted the Monday to be
> one, just as for the Sunday example, Sunday was 1.
> 2) Would the symmetry of this example break down if you wanted the 2nd
> Thursday because you cannot set Thursday to 1?

Yes, you would have to offset that.. The reason I added the weekday 2 option
is just that.

=A1-DAY(A1)-WEEKDAY(A1-DAY(A1),3)+15

will obviously return the second Tuesday



>
> A1-DAY(A1)-WEEKDAY(A1-DAY(A1),2)
>
> Yields the 22nd (=29-7) of February.  You are always going to "back-up"
into
> the previous month to a Sunday where you have a full week remaining in
prior
> month?  Now you add 15.
>
>
> 2nd Example:
>
> =DATE(YEAR(A1),MONTH(A1),15)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6))
>
> I understand the end result of the equation, though I do not understand
why
> you started off with the 15th of the current month.  So I am missing step
1.
> I suspect once I get a slight push, your solution will rapidly make sense.
> But right now, I am stumped.


Ok, the reason it starts with 15 is that the weekday of the 15th of the
month will
always be the third occurrence of that particular weekday, Now if the first
date is a Thursday (April 1st 2004)
how many days differ between Thursday and Monday? 3 days.
Now the second part of the formula depending on what day we use, 6 in this
example
for Mondays will always return the difference between the 15th and the
second Monday.
Weekday for 04/06/04 will return 3, 04/15/04 - 3 is a Monday. Now using
March 2004,
which start with a Monday, that means that 03/15/04 will be the third Monday
of the month
and 03/06/04 is a Saturday, Weekday for Saturday is 7, so 15 - 7 must be the
second Monday.
Since the weekday(date) will always return this internal indexing it will
always be correct.
I believe I picked this up from Daniel Maher who is in my opinion extremely
clever when it comes to dates.
So if you want the second Sunday, change the last day part to 7, 5 for
Tuesday..


>
> The reason for asking my questions is to better my understanding.  Like
most
> everything else, a bit of practice always helps.
>
> Thank you so much Peo.
>
> Best regards,
> Kevin


Regards,

Peo Sjoblom


0
terre081 (3244)
11/28/2003 11:33:18 PM
Peo,

Thank you so much for taking considerable time to respond to my question.

Best regards,
Kevin


0
stecyk (172)
11/29/2003 1:38:18 AM
David,

You asked this question on 27th Nov 2003, and this is the answer I gave
then.

Assuming you have a date in A1, this gives the 2nd Sunday of that date

DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"DavidObeid" <DavidObeid.xkpiy@excelforum-nospam.com> wrote in message
news:DavidObeid.xkpiy@excelforum-nospam.com...
>
> Can anyone help me write a formula to find the date of the second sunday
> in a given month?
>
> Thanks in Advance,
>
> Dave
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
1/20/2004 10:09:26 PM
Totally gone. This is the post from then - no wonder it looked familiar.

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23bUiWI63DHA.536@tk2msftngp13.phx.gbl...
> David,
>
> You asked this question on 27th Nov 2003, and this is the answer I gave
> then.
>
> Assuming you have a date in A1, this gives the 2nd Sunday of that date
>
> DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7
>
> -- 
>


0
bob.phillips1 (6510)
1/20/2004 10:23:39 PM

I hate when that happens...

<vbg>

BTW, if you post an empty message with the subject line 

cancel "The exact subject line of your message"

the news server will delete your post, and this will propagate to the
other servers.  Google may archive your faux pas in the meantime, but
you gotta leave your mark somehow!

I use this cancel thing more often than I'd care to count!!

-Mike


On Tue, 20 Jan 2004 22:23:39 -0000, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

>Totally gone. This is the post from then - no wonder it looked familiar.
>
>"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>news:%23bUiWI63DHA.536@tk2msftngp13.phx.gbl...
>> David,
>>
>> You asked this question on 27th Nov 2003, and this is the answer I gave
>> then.
>>
>> Assuming you have a date in A1, this gives the 2nd Sunday of that date
>>
>> DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7
>>
>> -- 
>>
>

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup 

To e-mail me, remove nospam from the address in the headers
0
mikeargy (62)
1/21/2004 12:58:36 AM
Reply:

Similar Artilces:

Report of sales by month based on a cross tab query
Hi, I have a cross tab query that will provide sales by month for 2008. Right now it is sales for January08. As there are sales for future months, they will appear as well. I want to create a table that has all of the months listed out already. Right now, on the first day of the month, I go into the report and add the new month. I want it so that all month are listed which I have done. The problem is that when I run the report, I get an error that says "the Microsoft Jet does not recognize February as a valid field name. After today, there will be February data but no March ...

Find and Replace within an XML file
I was wondering if there is a simple method of replacing characters within the xml document. I'm looking at writing a Biztalk Pipeline that escapes illegal characters before they are processed by a HL7 assembler. I.E. replace all instances of &amp; or & with \T\ . I thought about converting to a string and using the replace function, but that would mean knownig all the HTML codes for characters (as the escape characters are to be dynamically chosen by the user). Where as I could via xpath ??? replace all instances of the original character such as &. Hope it makes sen...

Excel Formulae #4
I've been trying hard to find a formula which would enable me (from one cell) to Add several cells and Subtract the answer from another cell (subtracting items of expenditure from a starting total). I could manage this with Lotus - but Excel does not seem to behave the same. Advice would be much appreciated. Thanks, Ken. One way: Say you wanted to subtract A1, A2, A3, J4 and N5 from L6: =L6-SUM(A1:A3,J4,N5) In article <173d01c3fbd6$3df0f300$a401280a@phx.gbl>, "KenS." <anonymous@discussions.microsoft.com> wrote: > I've been trying hard ...

Shorter Formula
Can anyone shorten this formula please. Basically all it does is gives me an average of the figures in Column "W" depending on the number of times that product appears in "R" column =IF(ISERROR(SUM(SUMIF($R$5:$R$9,R62,$W$5:$W$9),SUMIF ($R$22:$R$26,R62,$W$22:$W$26),SUMIF ($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF ($R$5:$R$43,R62)),0,SUM(SUMIF ($R$5:$R$9,R62,$W$5:$W$9),SUMIF ($R$22:$R$26,R62,$W$22:$W$26),SUMIF ($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF($R$5:$R$43,R62)) thanks Pete I didn't try too hard to analyze your formula, just noted that your ranges and sum_ ranges...

Formula counts incorrectly
Help please. Column K contains dates and blank cells. I would like to count how many of these dates fall within a given date range. For example, in the first week in May. Column K contains these dates. 5/2/2005, 5/3/2005, 5/5/2005, 5/5/2005. I would like to count this as 4 dates within the first week of May. I created this formula but the output is 3. =SUMPRODUCT(--(ECNT!F2:F515>=DATE(2005,5,2)),--(ECNT!F2:F515<=DATE(2005,5,6)),--(ISNUMBER(ECNT!K2:K515))) I assume that it is only counting 5/5/2005 one time? I changed the dates in the formula to check a second 2nd date range (5/9 to ...

VBA Code to Open File1; Open File2 copy sheets to File1
Using XL 2003 & 97 Need help with the following VBA code: Sub Macro1() Dim myNewWorkbook As String myNewWorkbook = ActiveWorkbook.Name Workbooks.Open Filename:="OldWorkbookFile.xls" Sheets(Array("Formulas", "Department Lables")).Activate Sheets(Array("Formulas", "Department Lables")).Copy _ Before:=Workbooks(myNewWorkbook).Sheets(2) Windows("OldWorkbookFile.xls").Activate ActiveWorkbook.Close Windows(myNewWorkbook).Activate ' ........ End Sub My goal is to have myNewWorkbook active then:...

Protect formatting & formula
I need to protect the formatting & formula in each cell to avoid accidental changes during update by different users. However, if I protect them, any new row creation and deletion are disable. Is there any way resolve it? Especially, when create a row, how can all formatting and formula to create automatically for the new row? Thanks, Scott What version of Excel? "Scott" <NoSpam-Scott.Xe@GMail.com> wrote in message news:%23bsg6kCBGHA.4092@TK2MSFTNGP09.phx.gbl... >I need to protect the formatting & formula in each cell to avoid accidental >changes d...

Is there a way to automatically put the $ sign into the formula?
I want to turn this =A1 into =$A$1. How is the best way of doing this. Is there a shortcut....keyboard stroke to do this?? Hit [F4] several times and watch the results. Micky "hrb_2001" wrote: > I want to turn this =A1 into =$A$1. > How is the best way of doing this. Is there a shortcut....keyboard stroke to > do this?? > Hi, Enter =A1 then tap F4 Repeated taps of F4 scroll through the referencing options -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently an...

VBA Combo Box
I have a combo box that I want to populate with a list of names. I do not want to reference names in the worksheet - using "RowSource" - I want to write the names in the code. I tried the following code: Private Sub ComboBox1_Change() With ComboBox1 ..RowSource = "" ..AddItem "Male" ..AddItem = "Female" End With End Sub This gives me an error. Does anyone know where the code should be added. Is this on a userform? If yes, then maybe it should be in the Userform_initialize procedure? If it's on a worksheet, maybe in the auto_open or workbook...

pie-charting non-numeric data
I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value...

Help writing a formula
I have a huge database that I need to create numbering for each line like this example: AK-233 (2 letter state, a dash, and 1-4 digit number), but the number doesn't change each line - sometimes the number will be duplicated on multiple lines because those lines have the same title, I also need to create another cell extending that letter/number combo that will differentiate the 1st cell. So the first cell stays the same if the title is the same. The second cell adds another layer of numbering to differentiate the first cell. See example: (Row1, Col 1-4) AK-1, AK-1-1, Title1...

Print dialog box using VBA in excel
Hi Everyone, I like to add a print button (that will select several worksheets and print them) in my excel worksheet. I have recorded my action using the macro recorder the problem is I can't select the printer everytime I run the macro it print in the background without asking the user to select the printer and uses the default printer. Can anyone help?? I just want the user to be able to select the desired printer every time before printing. My current code: Sub Print_All() Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 5)).Select 'intent...

Automatic Find and Populate
Ok, I have several spreadsheets that represent bills and have tabs that represent the months. So, when I recieve a new bill for the month, I copy last month's bill onto a new tab, and rename it using the billing cycle. Each year, for each account number is contained in its own workbook- ie: account 245-9999 has a workbook for 2002, 2003, 2004, and 2005; within that workbook are tabs for Jan, Feb, Mar, etc. Ok, on a single month's tab, there are anywhere from 10 to 900 individual line items representing circuits. What I want to do is create a separate spreadsheet that lists unique c...

Troubleshooting a non-delivery Problem
I am trying to troubleshoot an intermittant non-delivery problem to a public folder. I have a public foler that is set not to be replciated to the other exchnage servers in our exchange environment. End users create Outlook forms based messages that get sent to the public folder involved. All of the user accounts that use the forms involved are on the save server. The public folder is located on a differnt exchange server. On occassion, when submitting a message using a specific form, the user will get a delayed deliver message that is always followed a couple of days later by a non-deliver...

Excel 2003 - VBA
With code in Module 6, I created a button on a sheet as follows: ActiveSheet.Buttons.Add(1245, 16, 80, 16).Select Selection.Caption = "AutoFocus On" Selection.Name = "AutoFocusBut" Selection.OnAction = "FocusOn" Focus = True I placed the sub "FocusOn" also in Module 6. Public Sub FocusOn() If Focus = True Then .... .... When the button is depressed I get the message: "Ambigous name detected: FocusOn". What am I missing. Craig My bet is you have two 'things' named FocusOn. Two subs or a sub and a variable...

Re: Could someone please help me with formulas
sorry typo http://www.eaglepi.com/formula/example.xls "No" <no@isp.com> wrote in message news:... > I created a workbook and tried to explain the best I could how everything > should be. you can download the workbook at, > http://www.eaglepi.com/formlua/example.xls > > I really appreciate everyones help on this..... > > > "CLR" <croberts@tampabay.rr.com> wrote in message > news:%235IxtFEHFHA.2936@TK2MSFTNGP15.phx.gbl... > > Maybe in cell J2 you could put the formula =H2+I2, which would give you > the > > sum of the C...

Obtain Sub-Folder Names with VBA?
Is it possible to obtain the names of all Sub-Folders for a specific parent Folder? Just 2 levels (1 Parent Folder) and several children folders. We need the names of the sub-folders, not the names of the files in the sub-folders. Thanks for your help. Brad Lookup the Dir Function in the help file, specifically at the example it illustrates exactly how to get a directory listing. You'll see it isn't hard! -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the...

Slow load for Analysis Toolpak
I have a macro in a file that automatically loaded the Analysis Toolpak - VBA, but when I upgraded to Excel 2003, it takes FOREVER to load this add-in. Anyone know why or how to fix? No, but I don't think you need the macro. Just select Tools, Add-ins, Analysis toolpak and it should load every time. "RyanK" wrote: > I have a macro in a file that automatically loaded the Analysis Toolpak - VBA, but when I upgraded to Excel 2003, it takes FOREVER to load this add-in. Anyone know why or how to fix? ...

Failed Installation after 2nd 22 hours download!
Tks both Mary and John. Successfully downloaded Office Publisher to desktop, but installation failed. My 6 week old new system has caused me DAYS of unsuccessful attempts at rectifying Vista issues. I not able to do much more than surf the net with it. I'm at my wits end. I may just take the entire system back and wait until the tech issues have been resolved. I'm in the thick of setting up my own business and have spent more time trying to "fix" my computer than on my business set up. LOVE the Vista improvements, but the "EASE" of using XP is gone. This produc...

turning off formula bar
I can't remember or find how to turn off the formula bar in Excel 2003? Can someone help please. This is the bar that displays cell contents up top. Thanks! --Randy Starkey Tools>Options>View tab, uncheck Formula bar -- Kind regards, Niek Otten Microsoft MVP - Excel "Randy Starkey" <randy.starkeyNOSPAM@NOSPAMvictorychurch.com> wrote in message news:12kcghhahv0oif4@corp.supernews.com... |I can't remember or find how to turn off the formula bar in Excel 2003? Can | someone help please. This is the bar that displays cell contents up top. | | Thanks! | | --...

Report to show totals for each day of month
Greetings, I have an Excel spreadsheet that I use to capture: Column A) Day of the month (1-May, 2-May, etc.) Column B) How many clients were in residence on each day (Occupancy) Column C) Total Capacity (27) Column D) Shows a "1" if the Occupancy is 90% or more of the Total Capacity, else "0" The Totals row shows: Column B) The average Occupancy for the month Column D) Totals the times there is a "1" in Column D Is there a way for me to capture this data in a Report? I've got a query set up that uses these expressions: For AdmitDate: <=[Dat...

Copying non-adjacent columns to adjacent rows
Hi all, I consider myself fluent in Excel, but I've developed a situation that has stumped me. Any help would be much appreciated. I might be able to solve this issue if somebody could show me how to add a number to a column. For example, if I want Excel to pull data from Column D, how can I get Excel to realize that column D is really the same thing as Column A + 3? I know you can use the column() command to get the numerical value for a column, but is there a way to have it do that in reverse, such that you could tell it the column number is 4 and it would know that you are referring...

Formulas in a criteria area
Can a formula be used in a criteria area when extracting data from a table in Excel 2007? ...

Deleting multiple rows through a formula
So does anyone know a formula that will allow me to delete multiple rows of re-occuring data in a spreadsheet. For example, I have a spreadsheet of 10,000 lines, with multiple mobile numbers on it. I need to only have one of each mobile number and delete the remaining rows where the mobile has been repeated? Mike you may be able to use the advance filter where can select unique records only. select data filter advanced filter select copy to another location then in list range box enter range to filter in the copy to box enter a free column select unique records only then ok then just ...

How do I clear a column of data without clearing the formulas?
I want to zero out a column of data, but not the subtotal formulas. How can I do that easily without going to every cell? Hi Select your column then choose edit / goto / special - constants - ok this will just select the non-formula cells then press the delete key cheers JulieD "EllenSwarts" <EllenSwarts@discussions.microsoft.com> wrote in message news:DD518142-47BC-438D-BE23-8DF1E9F72042@microsoft.com... >I want to zero out a column of data, but not the subtotal formulas. How >can > I do that easily without going to every cell? qlso look at: http://www...