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
0
Pol (15)
10/30/2008 5:25:02 PM
excel 39879 articles. 2 followers. Follow

3 Replies
992 Views

Similar Articles

[PageSpeed] 57

=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 Saturdays in January of 2008.

But I'm not really sure what you have in A1 and B1.  Maybe the first and last
date of the month you're interested in?

If that's true, you could use:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),1)=1))
and
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),1)=7))




pol wrote:
> 
> 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

-- 

Dave Peterson
0
petersod (12005)
10/30/2008 7:04:22 PM
On Thu, 30 Oct 2008 10:25:02 -0700, pol <pol@discussions.microsoft.com> wrote:

>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

With some date in the month of interest in C1

A2:	Number of Saturdays
=5-(MONTH(36+C1-DAY(C1)-WEEKDAY(C1-DAY(C1)+1))<>MONTH(C1))

B2:	Number of Sundays
=5-(MONTH(36+C1-DAY(C1)-WEEKDAY(C1-DAY(C1)+7))<>MONTH(C1))

--ron
0
ronrosenfeld (3122)
10/30/2008 7:07:09 PM
thanks very much . I got the solution from ur reply




"Dave Peterson" wrote:

> =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 Saturdays in January of 2008.
> 
> But I'm not really sure what you have in A1 and B1.  Maybe the first and last
> date of the month you're interested in?
> 
> If that's true, you could use:
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),1)=1))
> and
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),1)=7))
> 
> 
> 
> 
> pol wrote:
> > 
> > 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
> 
> -- 
> 
> Dave Peterson
> 
0
Pol (15)
10/31/2008 10:11:03 AM
Reply:

Similar Artilces:

Sequential Numbering in Word 2003 and Recycling unused numbers
Once again, I am stuck in Macro world which is quickly consuming me :) It is though, quite addictive and I am determined to win the fight I'm having with my PC. I work for a company which uses network servers so the document needs to be accessed by different people but not necessarily at the same time. We operate Windows XP Word 2003. I am trying to create a document that will open with a sequential number, but if the document is not used or opened accidentally then the number it has generated needs to be recycled for auditing purposes. I seem to be able to get the number t...

Case numbering 01-26-04
I have found the auto numbering in 1.2, but it only lets me change the prefix for the cases "CAS". I need to start my numbering at 3500 instead of 1000. Is there a way to change the starting number? Thanks, Stefanie ...

how do Ii remove the grey page numbers in a work sheet
how do Ii remove the grey page numbers in a work sheet Try going to the View menu and unchecking Page Break Preview On Wed, 2 Feb 2005 11:01:08 -0800, "parker1230" <parker1230@discussions.microsoft.com> wrote: >how do Ii remove the grey page numbers in a work sheet You may be in page break preview mode. Go to "View" and select "normal" Regards Bill K "parker1230" <parker1230@discussions.microsoft.com> wrote in message news:5A65FC73-CAA0-428D-8ECD-0C2A2E15B799@microsoft.com... > how do Ii remove the grey page numbers in a work she...

How to you selectively sum on a series of numbers?
I have a column filled with numbers, but I only want to sum up a few of them. Let's say I have numbers filling A1:A10. Let's say I only want to sum up the numbers located is A3 and A4. I'd like to ability to enter something in the b column (e.g. "y" in cells b3 and b4) and have a formula that adds up the values in the "A" cells that have a corresponding "y" in the "B" column. This formula would allow me to selectively choose which cells needs to be summed. What is the best/cleanest way to write that formula? Thank you. =SUMPRODUC...

How do I reduce the number of data points I have??
Hi , I have some 10000 data points from which I need to reduce to something like 100 points before making an xy chart. Typically I would like to make a chart of every 10th point. So basically I need to reduce the length of my two rows. Is there any way I can do this easily?? Any help is highly appreciated. Thanks in advance -- swapan From the Microsoft Charting FAQ: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon024 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ ssan...

Automatic number fill-in on Labels
Is it possible to have word automatically fill in a series of numbers on Labels. I need a set of labels, counting by fives, from 1 to 3500. Just wishing I didn't have to key in every number....0001-0005, etc. See http://www.gmayor.com/Numbered_labels.htm. -- Stefan Blom Microsoft Word MVP "Bridgett" <Bridgett@discussions.microsoft.com> wrote in message news:9D380870-FF28-4227-B724-301AC3EC9A8D@microsoft.com... > Is it possible to have word automatically fill in a series of numbers on > Labels. I need a set of labels, counting by fives, from 1 ...

converting from text to numerical number
I have a spread sheet of entire year schedule. The format as following: 1 2 3 .... 31 row 1 row 2 row 3 Jan row 4 row 5 Feb row x Dec I would like to replace column 1 up to Jan to 1. Replace the cell below Jan to Feb to 2. etc. Would anyone help. Thanks in advance. You could select cells A1:A3, type a 1, then press Ctrl+Enter Select A4:A5, type a 2, and press Ctrl+Enter And so on, till all twelve months are done. james wrote: > I have a spread sheet of entire year schedule. The format as following: > > 1 2 3 .... 31 &...

Macro: Find matching DATA, copy correspondently values into another list
Okey guys - please advise me on this. (There was unfortunately no quick response to my latest problem - and I begun to swim... However - that brought me out on deep VBA-water...again - and now need a life buoy (metaphorical spoken)). The core of my macro problem is to look for all the values in column A (Range A5:A??) in Sheet "BOARD" (not bored!!!) that match values in another list placed in column A in sheet "LIST". When finding the match I will copy correspondent celle.value in column D / sheet "BOARD" into column B in sheet "LIST" - this w...

How to Find Record if Exists, Add if not?
How to Find Record if Exists, Add if not? I have a form where user will enter the vendor ID, which is a unique ID. They enter it off a long list of information listing many vendors and it's hard to know if the vendorID already exists in the database, until they tab through all the fields and get the duplicate record error. Then they press Esc and do a search on that vendorID to pull up the existing record to update. VendorID format is similar to this A123456789ST (Usees a prefix, Vendor FEIN, State abbrev.) I want to automate this. when they enter the VendorID, I want it to s...

Finding data
Hi all, first post here at these forums :) I have a question/ problem that hopefully someone can answer for me. If anyone has the time, could they have a look at the attachment, i was easier that trying to explain it. But in a nutshell it involve finding data within sheets and totaling them on another sheet. The problem is that in my real world example, I will have 40 sheets and 100 rows of data, so performing it manually is out of th question. cheers, and thanks for your time. Charli +------------------------------------------------------------------- |Filename: help1.zip ...

ADO Find method
I would like to know if there is a way to use the ADO Find method to find one of several values for a single column. See the following example: rstOpenJobProcess.Find "[proc#] = '940' or '941'" I would like to search for proc# '940' or '941'. I want to avoid using a like statement in the criteria (such as [proc#] Like '94*') because I may use '942' for something else in the future that I would not want to search for. Is there a way to use the Find method in this way? Alternatively, is there a clever workaround? Th...

group data according to month/year
hi, i have two columns of data. one column has date in that and the other has value in it. I like to group them according to month/year and find the subtotal of each month in one year. I like to accomplish it without using pivot table. is that possible? Mosaddeq You could add a helper column with a formula like: =text(a2,"yyyymm") and then sort your data by this column. Then apply subtotals to the range using that helper column as the key. Mosaddeq Aziz wrote: > > hi, > > i have two columns of data. one column has date in that and the other has > value in ...

Label of Number in a Range
I have set a high and low range of values with a correponding label, how do I return the label of the range where the data point fits? For example, ColA ColB ColC 5 10 Low 11 20 Medium 21 30 High D1 has the value 13 how do I get E1 to return the label "Medium" Thank you Hi Try: =VLOOKUP(D1,A2:C4,3) -- Andy. "webnntp" <webnntp@yahoo.com> wrote in message news:d1d35423.0405120719.7be94baa@posting.google.com... > I have set a high and low range of values with a correponding label, > how do I return the label of the range where the data point ...

Text Function with Different Formatting for Number
I'm trying to use a text function where I reference a number from another cell but I need the text part to be bold and the number not to be bold. This is the formula I'm using right now: ="Sales: "&TEXT(A16,"$#,##0_);($#,##0)") Unfortunately, excel won't let me just make the text bold without making the number bold. Anyone have any suggestions? Thanks M, I think a macro could do this, since it's really a string you're going to wind up with. But if you can put the Sales: part in a separate cell, life will be much easier anyway. then you ...

Converting a number to Hour & Min format
I am attempting to take a basic number, say 2000, and convert the number into an hour and minute format (i.e. 33:33) only. Everytime I use the custom format tab or the time/date tab I get a date included or an AM/PM setting. Any ideas or suggestions? Thanks in advance. Hi, For any number to be converted into time format you need to divide tha number by 24, since Excel time code is 24 (hr) code. then you forma the divided answer (83.33) into time, you will get 8:00 regards sazi -- Message posted from http://www.ExcelForum.com Use Format/Cells/Number/Custom [hh]:mm In article <...

length of lot number
increase the amount of characters in the lot number field. current field length is not ample space to adequately describe lots in our organization. Length of the item description was increased to 100. In our case, lots are as important as items. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agre...

16-year old completes Sail Around the World in 7 months
Australian teen completes round-the-world sail May 15, 5:36 AM (ET) By KRISTEN GELINEAU SYDNEY (AP) - A 16-year-old Australian who spent seven months at sea in her pink yacht sailed across the finish line of her round-the-world journey Saturday, becoming the youngest sailor to circle the globe solo, nonstop and unassisted. Thousands of spectators erupted into cheers as Jessica Watson sailed into Sydney Harbour, the finale to an epic adventure in which she battled 40-foot (12-meter) waves, homesickness and critics who said she'd never make it home alive. "She said ...

Advanced find and seeing full path to mail
When doing an advanced find Outlook only show the sub- folder it is in. How do you get it to list the full path. Listing a sub-folder can be very hard if there are folders with the same name. This is with Outlook 2000. ...

Car loan = downpayment and monthly payments in essential bidget
car purchase price $18,000 downpayment = $2,000 Car loan $16,000 Essential budget Car asset account "Chevy" set up ok car loan citibank setup ok 1) what is correct category for check issued for downpayment? At present I have it as Transfer: Chevy, but on the Chevy account setttings I have to indicate purchase price as $16,000 (not the $18,000 it cost). Is that correct or should I indicate some other category for the downpayment? 2) Citibank loan setup ok, and each payment show up as category "Loan Payment : Chevy car loan", but I can't make this categgory appear...

Vertical page numbers in a graph
I need to add vertical page numbers under page setup when printing a graph. Has anyone done this before? I definitely want it out of the graph area. Thanks - I don't understand the problem -- and I suspect a similar reason has stopped others from responding. If you set up a page number in the page header, it is not part of the chart area. In addition, after setting up the page information in the header, switch to Print Preview mode and click the margins... button. XL has a neat graphical way of adjusting various page borders/margins. -- Regards, Tushar Mehta, MS MVP -- E...

Ticket Numbering
I need to make 200 numbered tickets. I can design the ticket but how do I design them that they print with consecutive numbers? regards, "nobody" You will have to setup a data base with your numbers then do a mail merge. When you do a merge you can only have one ticket on your screen. Select multiple pages per sheet in the print setup. There is help here: http://office.microsoft.com/en-us/publisher/CH100502901033.aspx -- Mary Sauer http://msauer.mvps.org/ "nobody" <nobody@nowhere.com> wrote in message news:OsyqzgcxKHA.3304@TK2MSFTNGP...

Asked before cannot find question or answer anymore "Caracter mess
When I am using Word I have no problem typing this caracter ? (question mark). however if I type this in OOUTLOOK, than it appears like this É or like this _ It has just done it lately, so I must have changed a setting unknowingly. Can someone please help to get the question mark ? back again... thank you. By the way this time I have checked the "Notify me of repies" Verify your keyboard language and layout settings in Regional and Language option in Control Panel. For more details see; http://www.msoutlook.info/question/16 -- Robert Sparnaaij [MVP-Outlook] Coauthor, C...

transaction number in PC Charge
Is there any way to manage the fields that should go to PC Charge while tendering credit cards in RMS. The problem is that transaction number ( called Ticket in PC Charge) doesn't appear in PC Charge and that cost as extra money. Regards, Ewa ...

How can I superscript numbers greater than 3?
I need to add footnotes to an Excel spreadsheet. I found the numbers 1 thru 3 in the insert symbols menu, but I need many more numbers subscripted. Can anyone help? Try Format, Cells, font, effect (superscript, subscript) Carole O "Caseybay" wrote: > I need to add footnotes to an Excel spreadsheet. I found the numbers 1 thru 3 > in the insert symbols menu, but I need many more numbers subscripted. Can > anyone help? Thanks so much, Carole! That's perfect for what I want. I must have seen that dialog box a million times but never related it to what I needed in t...

Find a "9" within a range using a function (T/F)
I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false; I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas? Thanks in advance! -- RMC,CPA Hi! Try this: =SUMPRODUCT(--(ISNUMBE...