# 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.  Register To Reply

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)  Register To Reply

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.  Register To Reply

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!  Register To Reply

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.  Register To Reply

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  Register To Reply