# I'm lost here. Formula to calculate % per day of the week, month 100%

1. ## I'm lost here. Formula to calculate % per day of the week, month 100%

Ok, this seems simple enough for someone who may know the correct function/formula.

Basically this.

Saturdays are always 2%
The rest of the work days are equal, but the total for all can only be 100%

In the spreadsheet it looks like this

THUR %
FRI %
SAT 2%
MON %
TUES %
WED %

and so on. for the current month. There could be more saturday's in a month than 4. I'm needing something that looks for the text SAT and multiplies the number of it finds by 2, that will give a number. Subtract that from 100, then spread the % out evenly to each weekday.

2. ## Re: I'm lost here. Formula to calculate % per day of the week, month 100%

A1 = date with 1st day of month (e.g. mm/01/yyyy),

Weekday % =(100-2*IF((EDATE(A1,1)-A1)+WEEKDAY(A1,1)<35,4,5))/(EDATE(A1,1)-A1-4)

3. ## Re: I'm lost here. Formula to calculate % per day of the week, month 100%

Another, albeit lengthier, formula:
Formula:
`Please Login or Register  to view this content.`
This formula works for the layout in the attached spreadsheet which may or may not be what you had in mind. If not please click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window to upload your vision of the spreadsheet.
Let me know if you have any questions.

4. ## Re: I'm lost here. Formula to calculate % per day of the week, month 100%

Originally Posted by JeteMc
Another, albeit lengthier, formula:
Formula:
`Please Login or Register  to view this content.`
This formula works for the layout in the attached spreadsheet which may or may not be what you had in mind. If not please click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window to upload your vision of the spreadsheet.
Let me know if you have any questions.
JeteMc

This formula works and I was able to use it in a slightly modified form to get what I needed. Many thanks for everyone for some great fixes!

5. ## Re: I'm lost here. Formula to calculate % per day of the week, month 100%

You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

6. ## Re: I'm lost here. Formula to calculate % per day of the week, month 100%

after reading JeteMc's post had realised I've missed out the Sundays in the calculations.
correct formula should be

=(100-2*IF((EDATE(A1,1)-A1)+WEEKDAY(A1,1)<35,4,5))/(NETWORKDAYS(A1,EDATE(A1,1)))

with date (mm/01/yyyy) in A1

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1