+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    10-06-2014
    Location
    Greenville, SC
    MS-Off Ver
    2010
    Posts
    27

    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. #2
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    343

    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. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,854

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

    Another, albeit lengthier, formula:
    Formula: copy to clipboard
    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Registered User
    Join Date
    10-06-2014
    Location
    Greenville, SC
    MS-Off Ver
    2010
    Posts
    27

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

    Quote Originally Posted by JeteMc View Post
    Another, albeit lengthier, formula:
    Formula: copy to clipboard
    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. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,854

    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. #6
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    343

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculate Week Range from Week Number, Month and Year?
    By A108A108 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-07-2016, 03:11 PM
  2. VBA to calculate AVERAGEIF and SUMIF for current week/month/year
    By lostest in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-21-2016, 07:35 AM
  3. Replies: 10
    Last Post: 03-15-2016, 05:16 PM
  4. [SOLVED] calculate numbers of days elapsed a week, month or 6 months from now
    By labogola in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-01-2014, 09:14 AM
  5. Calculate # of sales per week, month & quarter
    By AlcatrazT2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2013, 06:25 PM
  6. Calculate week number in month
    By PRodgers in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-19-2010, 11:38 AM
  7. [SOLVED] Excel: Is there a way to calculate the date as week of month?
    By debra adams in forum Excel General
    Replies: 2
    Last Post: 01-03-2005, 06:06 PM

Bookmarks

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