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

[Juan Correa]

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

 

[Mike H]

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]

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

 

[Luke M]

Weekdays:
=INT(A2/7)*5+MOD(A2,7)

Weekends:
=A2-B2

 

[Mike H]

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.

 

[Mike H]

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.

 

[Juan Correa]

Yep... works like a charm. Thanks a million.

Cheers
JC

 

[Mike H]

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.