| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find number of weekdays and wekend days given a total number of da
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find number of weekdays and wekend days given a total number o
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find number of weekdays and wekend days given a total number of da
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find number of weekdays and wekend days given a total number o
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find number of weekdays and wekend days given a total number of da
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find number of weekdays and wekend days given a total number o
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find number of weekdays and wekend days given a total number o
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Running total over a find number of days | Excel Worksheet Functions | |||
| Calculating total number of days | Excel Worksheet Functions | |||
| Formating Total Number of Days | Excel Worksheet Functions | |||
| Total number of days between 2 dates | New Users to Excel | |||
| How do I find the total number of the same number/letter in a row | Excel Discussion (Misc queries) | |||
Hybrid Mode
