Find number of weekdays and wekend days given a total number of da

Hello,

I have a question for the gurus here.
I am working on a spreadsheet where in column A I have Total # of Vacation 
days.
Example:
# Vac Days   ---   # Weekdays   ---   # Weekend days
     75                          55                        20
     44                          32                        12
     25                          19                         6
I figured this out by simply creating a list like this
1  Weekday
2  Weekday
3  Weekday
4  Weekday
5  Weekday
6  Weekend
7  Weekend
And so on...
And then doing a countif to figure out how many of either on a given range.
Setting up this list is not practical since I'd have to adjust each formula 
to only take the correct range into account when counting the days, and doing 
this on a spreadsheet with hundreds of rows of data would take too long.
So I'd like to know if there is a way that this could be figured out 
withouth the COUNTIF.  Assuming that day 1 is always Monday is OK.  And no 
need to account for Hollidays.

Thanks
Juan Correa

0
Utf
2/19/2010 8:53:01 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
613 Views

Similar Articles

[PageSpeed] 48

Hi,

Please clarify. In column A you have a number (say) 75 and the first of 
these days (day 1) is a Monday, Is that correct?


-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Juan Correa" wrote:

> Hello,
> 
> I have a question for the gurus here.
> I am working on a spreadsheet where in column A I have Total # of Vacation 
> days.
> Example:
> # Vac Days   ---   # Weekdays   ---   # Weekend days
>      75                          55                        20
>      44                          32                        12
>      25                          19                         6
> I figured this out by simply creating a list like this
> 1  Weekday
> 2  Weekday
> 3  Weekday
> 4  Weekday
> 5  Weekday
> 6  Weekend
> 7  Weekend
> And so on...
> And then doing a countif to figure out how many of either on a given range.
> Setting up this list is not practical since I'd have to adjust each formula 
> to only take the correct range into account when counting the days, and doing 
> this on a spreadsheet with hundreds of rows of data would take too long.
> So I'd like to know if there is a way that this could be figured out 
> withouth the COUNTIF.  Assuming that day 1 is always Monday is OK.  And no 
> need to account for Hollidays.
> 
> Thanks
> Juan Correa
> 
0
Utf
2/19/2010 8:59:01 PM
That is correct.
In column A I can have any number.  75 is just one of them right now.  I 
have been told to assume that day 1 is Monday as well.

thanks Mike

"Mike H" wrote:

> Hi,
> 
> Please clarify. In column A you have a number (say) 75 and the first of 
> these days (day 1) is a Monday, Is that correct?
> 
> 
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "Juan Correa" wrote:
> 
> > Hello,
> > 
> > I have a question for the gurus here.
> > I am working on a spreadsheet where in column A I have Total # of Vacation 
> > days.
> > Example:
> > # Vac Days   ---   # Weekdays   ---   # Weekend days
> >      75                          55                        20
> >      44                          32                        12
> >      25                          19                         6
> > I figured this out by simply creating a list like this
> > 1  Weekday
> > 2  Weekday
> > 3  Weekday
> > 4  Weekday
> > 5  Weekday
> > 6  Weekend
> > 7  Weekend
> > And so on...
> > And then doing a countif to figure out how many of either on a given range.
> > Setting up this list is not practical since I'd have to adjust each formula 
> > to only take the correct range into account when counting the days, and doing 
> > this on a spreadsheet with hundreds of rows of data would take too long.
> > So I'd like to know if there is a way that this could be figured out 
> > withouth the COUNTIF.  Assuming that day 1 is always Monday is OK.  And no 
> > need to account for Hollidays.
> > 
> > Thanks
> > Juan Correa
> > 
0
Utf
2/19/2010 9:02:02 PM
Weekdays:
=INT(A2/7)*5+MOD(A2,7)

Weekends:
=A2-B2
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Juan Correa" wrote:

> Hello,
> 
> I have a question for the gurus here.
> I am working on a spreadsheet where in column A I have Total # of Vacation 
> days.
> Example:
> # Vac Days   ---   # Weekdays   ---   # Weekend days
>      75                          55                        20
>      44                          32                        12
>      25                          19                         6
> I figured this out by simply creating a list like this
> 1  Weekday
> 2  Weekday
> 3  Weekday
> 4  Weekday
> 5  Weekday
> 6  Weekend
> 7  Weekend
> And so on...
> And then doing a countif to figure out how many of either on a given range.
> Setting up this list is not practical since I'd have to adjust each formula 
> to only take the correct range into account when counting the days, and doing 
> this on a spreadsheet with hundreds of rows of data would take too long.
> So I'd like to know if there is a way that this could be figured out 
> withouth the COUNTIF.  Assuming that day 1 is always Monday is OK.  And no 
> need to account for Hollidays.
> 
> Thanks
> Juan Correa
> 
0
Utf
2/19/2010 9:03:01 PM
Hi,

I think I answered my own question by reading your post more carefully.

For weekdays
=5*INT(A1/7)+MIN(MOD(A1,7),5)

and weekend days
=A1-(5*INT(A1/7)+MIN(MOD(A1,7),5))
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Juan Correa" wrote:

> Hello,
> 
> I have a question for the gurus here.
> I am working on a spreadsheet where in column A I have Total # of Vacation 
> days.
> Example:
> # Vac Days   ---   # Weekdays   ---   # Weekend days
>      75                          55                        20
>      44                          32                        12
>      25                          19                         6
> I figured this out by simply creating a list like this
> 1  Weekday
> 2  Weekday
> 3  Weekday
> 4  Weekday
> 5  Weekday
> 6  Weekend
> 7  Weekend
> And so on...
> And then doing a countif to figure out how many of either on a given range.
> Setting up this list is not practical since I'd have to adjust each formula 
> to only take the correct range into account when counting the days, and doing 
> this on a spreadsheet with hundreds of rows of data would take too long.
> So I'd like to know if there is a way that this could be figured out 
> withouth the COUNTIF.  Assuming that day 1 is always Monday is OK.  And no 
> need to account for Hollidays.
> 
> Thanks
> Juan Correa
> 
0
Utf
2/19/2010 9:03:02 PM
Luke,

That will fall over for 6 days

-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Luke M" wrote:

> Weekdays:
> =INT(A2/7)*5+MOD(A2,7)
> 
> Weekends:
> =A2-B2
> -- 
> Best Regards,
> 
> Luke M
> *Remember to click "yes" if this post helped you!*
> 
> 
> "Juan Correa" wrote:
> 
> > Hello,
> > 
> > I have a question for the gurus here.
> > I am working on a spreadsheet where in column A I have Total # of Vacation 
> > days.
> > Example:
> > # Vac Days   ---   # Weekdays   ---   # Weekend days
> >      75                          55                        20
> >      44                          32                        12
> >      25                          19                         6
> > I figured this out by simply creating a list like this
> > 1  Weekday
> > 2  Weekday
> > 3  Weekday
> > 4  Weekday
> > 5  Weekday
> > 6  Weekend
> > 7  Weekend
> > And so on...
> > And then doing a countif to figure out how many of either on a given range.
> > Setting up this list is not practical since I'd have to adjust each formula 
> > to only take the correct range into account when counting the days, and doing 
> > this on a spreadsheet with hundreds of rows of data would take too long.
> > So I'd like to know if there is a way that this could be figured out 
> > withouth the COUNTIF.  Assuming that day 1 is always Monday is OK.  And no 
> > need to account for Hollidays.
> > 
> > Thanks
> > Juan Correa
> > 
0
Utf
2/19/2010 9:14:05 PM
Yep... works like a charm.  Thanks a million.

Cheers
JC

"Mike H" wrote:

> Hi,
> 
> I think I answered my own question by reading your post more carefully.
> 
> For weekdays
> =5*INT(A1/7)+MIN(MOD(A1,7),5)
> 
> and weekend days
> =A1-(5*INT(A1/7)+MIN(MOD(A1,7),5))
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "Juan Correa" wrote:
> 
> > Hello,
> > 
> > I have a question for the gurus here.
> > I am working on a spreadsheet where in column A I have Total # of Vacation 
> > days.
> > Example:
> > # Vac Days   ---   # Weekdays   ---   # Weekend days
> >      75                          55                        20
> >      44                          32                        12
> >      25                          19                         6
> > I figured this out by simply creating a list like this
> > 1  Weekday
> > 2  Weekday
> > 3  Weekday
> > 4  Weekday
> > 5  Weekday
> > 6  Weekend
> > 7  Weekend
> > And so on...
> > And then doing a countif to figure out how many of either on a given range.
> > Setting up this list is not practical since I'd have to adjust each formula 
> > to only take the correct range into account when counting the days, and doing 
> > this on a spreadsheet with hundreds of rows of data would take too long.
> > So I'd like to know if there is a way that this could be figured out 
> > withouth the COUNTIF.  Assuming that day 1 is always Monday is OK.  And no 
> > need to account for Hollidays.
> > 
> > Thanks
> > Juan Correa
> > 
0
Utf
2/19/2010 9:45:01 PM
Glad I could help and thanks for the feedback
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Juan Correa" wrote:

> Yep... works like a charm.  Thanks a million.
> 
> Cheers
> JC
> 
> "Mike H" wrote:
> 
> > Hi,
> > 
> > I think I answered my own question by reading your post more carefully.
> > 
> > For weekdays
> > =5*INT(A1/7)+MIN(MOD(A1,7),5)
> > 
> > and weekend days
> > =A1-(5*INT(A1/7)+MIN(MOD(A1,7),5))
> > -- 
> > Mike
> > 
> > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > introduces the fewest assumptions while still sufficiently answering the 
> > question.
> > 
> > 
> > "Juan Correa" wrote:
> > 
> > > Hello,
> > > 
> > > I have a question for the gurus here.
> > > I am working on a spreadsheet where in column A I have Total # of Vacation 
> > > days.
> > > Example:
> > > # Vac Days   ---   # Weekdays   ---   # Weekend days
> > >      75                          55                        20
> > >      44                          32                        12
> > >      25                          19                         6
> > > I figured this out by simply creating a list like this
> > > 1  Weekday
> > > 2  Weekday
> > > 3  Weekday
> > > 4  Weekday
> > > 5  Weekday
> > > 6  Weekend
> > > 7  Weekend
> > > And so on...
> > > And then doing a countif to figure out how many of either on a given range.
> > > Setting up this list is not practical since I'd have to adjust each formula 
> > > to only take the correct range into account when counting the days, and doing 
> > > this on a spreadsheet with hundreds of rows of data would take too long.
> > > So I'd like to know if there is a way that this could be figured out 
> > > withouth the COUNTIF.  Assuming that day 1 is always Monday is OK.  And no 
> > > need to account for Hollidays.
> > > 
> > > Thanks
> > > Juan Correa
> > > 
0
Utf
2/19/2010 9:47:01 PM
Reply:

Similar Artilces:

Adding floating point numbers
hi the value 0.1 in the computer memory is not equal 0.1 so if u add it 10 times the result is diffrent then 1.0: double value=0.1, result; ressult=value+value+value+value+value+value+value+value+value+value; //result<1.0 BUT: result=value*10; //result==1.0 WHY??? another example: float value=0.1, result; ressult=value+value+value+value+value+value+value+value+value+value; //result==1.0 WHY??? can anyone explain it? thx On Thu, 28 Jun 2007 10:18:05 -0700, rsobies <rsobies@discussions.microsoft.com> wrote: >hi > >the value 0.1 in the computer memory is not equal 0.1...

Getting rid of "fax" numbers when sending e-mail
I use Outlook XP for e-mail and also for contacts. I use my "Contacts" folder as my address book. Whenever I send an e-mail, Outlook always asks me to choose which address I want for the contact, and it brings up both the e-mail address and the fax number. So I have to go through an extra step almost every time of choosing which I want. Is there any way to stop it from bringing up the "Fax" number? See http://www.slipstick.com/contacts/nofax.htm Lee Daum wrote: > I use Outlook XP for e-mail and also for contacts. I use my > "Contacts" folder as my add...

Wndws XPsp3: Auto Update ON but system nags all day to Install Upd
I have my Auto Update set for AUTOMATIC to download/install at 3 am daily BUT I get at least 4-5 messages per day that "Updates have been found formy computer". Constantly updating/restarting. This is driving me CRAZY. Please help. ME wrote: > I have my Auto Update set for AUTOMATIC to download/install at 3 am > daily BUT I get at least 4-5 messages per day that "Updates have > been found formy computer". Constantly updating/restarting. This is > driving me CRAZY. Please help. We now know the following blanks in the puzzle... Windows XP ______ ...

phone numbers
Hello: You know how you cannot integrate employees into Employee Maintenance in Integration Manager if the employee's phone number has parentheses and dashes in it? Well, I found a great TechKnowledge article that walks you through how to replace the parentheses and dashes and successfully integrate. But, if you have one employee in your integration file that does not have a phone number, then Integration Manager throws out the employee record altogether and says "Invalid use of Null: 'Replace'". So, I tried the code below. I added some lines to the TechKnowledg...

Input from remote 10-key and laptop numbers very slow
The last few days I've experience a delay when inputting numbers into Excel 2000 worksheets. When I type in the numbers, they slowly enter themselves, one at a time pausing between each number. This is occurring both with the remote 10-key for the laptop and the number keys on the laptop. This is occuring in all files, no matter how big or small. I wouldn't say I have any overly complicated workbooks, mostly basic functions. I have had much bigger and more complicated before without a problem. When using the 10-key or the number keys anywhere else, Word, internet, e...

How do i number a list of data in excel 2003?
I was shown once, but I can't remember how it was done. I have a list of 5000 things and I need to number then 1-5000, but don't want to go through all 5000. I have seen it done where you type the first few like 1-7 and then use a shortcut and drag it down the rest of the list and the numbers will populate. Please help! You can try something like this: =IF(B1="","",COUNTA($B$1:B1) copy down....or if there are no gabs in your list simply double click on the lower right corner of the cell. HTH JG -- pinmaster -------------------------------------------------...

Cell Number Format includeing other Cell Value
Hello, I needed to create some conditional number format - to say so... My goal: Cell A1 content is "m2" as I Enter in cell A2: "10" -> the output to cell A2 should be "10 m2". Cell A1: "Dollars" -> A2: 200 -> shown "200 Dollars"... and so on.... Is that possible in Excel? (don't know VBA - yet :DD) Any help is appreciated! BR, Daniel How about just use a third cell: =a2 & " " & a1 daniel_of_vienna wrote: > > Hello, > > I needed to create some conditional number format - to say so... > M...

I have 5,00 images that need renamed to part numbers
I have 5,000 images I have to change to current name to a part # found on an excel spreadsheet. How do I change the actual name (not one at a time) but globally with these part #'s? On 2/1/10 4:01 PM, Elizabeth wrote: > I have 5,000 images I have to change to current name to a part # found on an > excel spreadsheet. How do I change the actual name (not one at a time) but > globally with these part #'s? This seems like something that you could do with VBA as long as there is a way to identify the new name from the old name (is the old name listed in the Excel ...

Interesting challenge to highlight instances of >10 consecutive days scheduled
This is a multi-part message in MIME format. ------=_NextPart_000_0034_01C3E99D.D9DCF660 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In column A (A12:A377) I have this year's dates by day. In a cell in = another column, let's say cell D1, I want to put in a calculation that = will present the word "OVER-SCHEDULED" when there is ever a time when = someone has been scheduled in to work more that 10 consecutive days in a = row. The calculation must look for the following schedule codes to = determine a work shift: D,E,N...

Check value range, then return spesific number?
Hi. I want to implement a function in my spreadsheet, that gives me this: When I enter a number into a cell, another number shows in another cell, based upon the number first entered. A bit more spesific: The idea is to calculate the number of instructors needed for a week end seminar. 1 participant requires 1 instructor. Same for 2 participants, and 3. 4, 5 and 6 participants requires 2 instructors. 7, 8 and 9 participants requires 3 instructors. And so on. For every 3 participant, we apply 1 instructor. So, i.e. if I enter the number 5, i want the number 2 in the cell below. I trie...

FBI FORENSICS CAN FIND DELETED FILES xxxaaaaaaaaaaaassssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssaaaaaaaaa\a
You're in Serious Trouble - It's a Proven Fact! FBI FORENSICS CAN FIND DELETED FILES THERE ARE THINGS ON YOUR COMPUTER THAT YOU THOUGHT YOU DELETED. !!!!BUT THEY ARE STILL THERE!!! Click here for details on the EVIDENCE ELIMINATOR Clear your computer of incriminating files that you cannot delete. www.removefiles.com THIS IS A MUST READ!!!! www.removefiles.com ewfrgh32gh02439-eftrpewjtrrewgfs dg fd g fds g fds g fds g fds g fds gfds g fd g fdasdsadsadsad fds g fd g fdsg jbn...

priv+stm file much bigger than total pst files
Exchange 2003 SP2/Windows 2002 SP4 Some time ago I added an exchange server, and moved some mailboxes to the new server. Due to problems with speed etc. on the new server, the mailboxes were moved back and forth a few times. I found that the stm file suddenly exploded to a very big size. Thinking this would be resolved with an offline defrag, I tried that with no change at all. I have made pst files of the mailboxes and the figures are: Total of pst files: 9Gb priv1.edb: 7Gb priv1.stm: 33Gb I have run offline defrag again with no change in database size (surprisingly I expected some reduct...

set first day of the week
hi, how to change the first day of the week (that my boss use a weekly reports) set datefirst 6 statment, completed succesfully but with no changing of default setting ( 7 ) ,any help please thanks ...

how to change numbers into words,
In excel how to change numberical data into english words, many times currency figures are required to be stated into english words as well so what is the formula for this problem, for example for US$ 5400 : $ Five thousand four hundred is the conversion into words. See http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH RP (remove nothere from the email address if mailing direct) "Mukesh Dhoot" <Mukesh Dhoot @discussions.microsoft.com> wrote in message news:B59710A8-2377-4CF5-98B0-229933A3DE33@microsoft.com... > In excel how to change numberical data into e...

Where to find Product key in Windows.old folder?
I upgraded my computer to Windows 7 Pro. I did not remember to save the product key for my MS Ultimate Office 2007 I purchased from Digital River last year. I cannot get the product key from Digital River anymore. I know the key is saved somewhere in the windows.old folder, but i cannot determine where the files is located. please help. -- AZDiablo "undisclosed" wrote in message news:9792f4744e43417cc7b3bde5581351cd@nntp-gateway.com... > > I upgraded my computer to Windows 7 Pro. I did not remember to save the > product key for my MS Ultimate Offi...

wrong subform total
I have a subform called "InvoiceDetailssubform" and a main form called "invoices details" in the subform there is a field Expenseitemamount so to get total of this field ,i have places a text box in the footer of the subform , which i called it "Expense Totals" now i want to get this figure for each record of the form " invoices details" but unfortunatley am i getting wrong figure , i get the total amount of all records in the source table of the subform . although i am using the same way in another main and subform , and getting correct results. w...

I have a column of numbers, some negative, some positive
I have some calulations in my worksheet. They are done twice, if my number is positive or negative. I need to fill the cell next to my number with the calulation that correspondes with the + or - of my number. if A1 + if A1 - A1 B1 C1 D1 -10 7 12 A2 B2 C2 D2 14 4 6 I need the 12 to appear in B1. and the 4 to appear in B2. Thanks In B1 enter =IF(A1<0,D1,C1) Copy down. Gord Dibben M...

Adding spaces between numbers in a cell that contains a full address
Hello, Sample data: 3-764 Neighbourhood Cir 39 Frostbite Lane 3938 Stardust Drive 4 Jones Lane I have a spreadsheet that contains addresses in a column such as the ones above. What I need to do is separate the numbers in the addresses in one of two ways. Either would be ok. The first is (and better for me) would be so that the number portion of the address would have spaces between every number, and then the rest of the addresses in the same cell. For example, "3-764 Neighbourhood Cir", would become "3 - 7 6 4 Neighbourhood Cir". The second would be to split the num...

Database query with parameters given in the sheet
Hi all, I've created a new database query with inner joins and sorting, which gives great results. But, there's only one thing I can't figure out on how to get this thing working... I'm using Office XP, and I try to make a sheet in which a user can specify a "from" date and a "to" date, after which the query starts only in this range. However, when I try to create the criteria, there's no way I can use variables or named cells in any way. Can anybody tell me a workaround for this matter? Maybe this is something for Microsoft to pun in new versions in...

Budget Numbers...Where from?
When I look at my Budget, I see an amount in the "Budgeted" column in the "Special :Debt" catagory that I don't recognize. It is a different amount each month. Can anyone tell me where Money gets this amount from? "Jimbob" <Jimbob@discussions.microsoft.com> wrote in message news:C3506374-D004-47E3-B010-61F1EA695A88@microsoft.com... > When I look at my Budget, I see an amount in the "Budgeted" column in the > "Special :Debt" catagory that I don't recognize. It is a different amount > each month. Can anyone tell me wher...

Formula using work days
I am trying to set up a formula in excel based on an issue priority ranking of emergency, high, medium and low. If an issue is of emergency priority, I want excel to add 2 workdays to the issues start date (which has already been defined in another cell). If the issue is of high priority, add 1 work week and if it is of medium or low priority; add 2 work weeks. Any thoughts? Make sure ATP is installed =IF(Other_cell="","",IF(Other_cell="High",WORKDAY(Cell_with_date,2,Holidays),IF(Other_cell="Medium",WORKDAY(Cell_with_date,7,Holidays),WORKDAY(Cell_...

Redeployment Wizard Can't Find Database
I have retored the CRM DB to the SQL Server and am able to access it via SSMS. When I run the redeployment wizard on the App server it give the message "Unable to retrieve a list of databases from the specified server" If I point the redployment wizard to our PROD or TEST servers it sees those databases. Mark; Sounds like a sql permission error - do you have the sysadmin role on the specified server? Also, make sure you have both the _MSCRM and the _METABASE databases restored, and that the prefix of the database names are identical. Dave Ireland "Mark" <Mar...

Dynamic Date Calculation
Hello is there a formula that will allow me to show the current age of someone, based on what is entered as their birthday. So the columns would be Member age B'day Formula here Typed in manually here. It would preferable for the Member age to be dynamic, so that as time progresses, the age will update automatically....if that is possible. Thanks for any suggestions. Hey Mike C you could use this formula, where the birthday is in B2: =YEAR(TODAY())-YEAR(B2)- IF(TODAY()>=DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)),0,1) I guess there's a shorter formula, but at least it w...

Advanced Find and Reports 02-07-06
I am attempting to create a report using the Advanced Find option and am experiencing a problem. I have a number of nested topics configured under Subject, but I seem unable to report on items below the top level. For example, if I have the following: Cars Ford Subaru Volvo Is it possible to simply select Cars within the Subject field and have it recurse through the tree (ie. include all branches)? ...

How can I get the sum of a column with an arbitrary number of rows?
I am trying to get one cell to return the value of an entire column on another worksheet for which the last row is always changing. If I use: =SUM(Sheet1!$A$2:A25) Then the cel will be broken once I add a row 26 to Sheet 1. What I would like to do is create a sum of the column no matter how many rows there are. Thanks. "Mike F." <nospam@spamless.com> wrote in message news:4coomvgodg9lvcp66cp9bddcrnhov8ap00@4ax.com... > I am trying to get one cell to return the value of an entire column on > another worksheet for which the last row is always changing. If I use: >...