+ Reply to Thread
Results 1 to 5 of 5

Calculate based on weekdays & weekends

  1. #1
    Registered User
    Join Date
    03-06-2009
    Location
    Malaysia
    MS-Off Ver
    Excel 2010 and 2013
    Posts
    47

    Calculate based on weekdays & weekends

    Dear Friends,

    I'm trying to calculate the rates for services where weekday & weekend rate is different (file attached). I cannot directly use NETWORKDAY function because of a difficult criteria given. For example, if I start on 1 March (Sunday) and Finish on 2 March (Monday), the rate is a Weekend rate and it's 1 day only. Unless I finish on 3 March, the rate is 1 Weekend & 1 Weekday.

    After trying for 2 long days, I thought I got the answer but the formulas worked only for March. When i test for April, the calculation is wrong again. Any thoughts what am I missing? Thanks.

    - Ichigo
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculate based on weekdays & weekends

    Assuming I understood this correctly, you still can use NETWORKDAYS(), just subtract weekdays from the total count to get the number of weekends, and multiply that by your weekend rate.

    Please Login or Register  to view this content.
    Using your example of starting on 1 April 2015 and ending on 4 April 2015, that formula results in 500, which makes sense to me, since 1 April thru 3 April are weekdays (rated at 100 based on Type A), and 4 April is a weekend (rated at 200 based on Type A). 300+200=500.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    03-06-2009
    Location
    Malaysia
    MS-Off Ver
    Excel 2010 and 2013
    Posts
    47

    Re: Calculate based on weekdays & weekends

    Hi mcmahobt,

    Thank you for your reply. I applied the formula and did some tests.
    • I input some dates and it didn't calculate accurately, especially the weekdays.
      Example: Start 1 March, Finish 2 March should be 200, because it's a weekend rate. Answer shows 300.
      Example: Start 1 Apr, Finish 3 Apr should be 200 because it's a weekday rate. Answer shows 300.
      But if i test with a weekend as Finish, it calculates correctly.
    • Is there a way to display 2 separate formulas as a breakdown to check total weekday & weekend, multiply to get the price, then sum up both rates at the last line?
    Thanks again.

    - Ichigo

  4. #4
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculate based on weekdays & weekends

    Quote Originally Posted by Ichigo View Post
    Hi mcmahobt,
    I input some dates and it didn't calculate accurately, especially the weekdays.
    Example: Start 1 March, Finish 2 March should be 200, because it's a weekend rate. Answer shows 300.
    Example: Start 1 Apr, Finish 3 Apr should be 200 because it's a weekday rate. Answer shows 300.
    I'm not sure I'm understanding your logic then with these calculations. 1 April to 3 April is three separate days. That would be three days times the weekday rate. 1 March to 2 March are two separate days, one weekend (200) and one weekday (100). It sounds like you are dealing with hours rather than days, and should maybe consider working with hours instead.

  5. #5
    Registered User
    Join Date
    03-06-2009
    Location
    Malaysia
    MS-Off Ver
    Excel 2010 and 2013
    Posts
    47

    Re: Calculate based on weekdays & weekends

    Hi mcmahobt,

    Sorry if i did not explain clearer. What makes it difficult for me is although 1 - 3 Apr is actually 3 weekdays, my answer is supposed to display 2 weekdays in cell B14 Normal. If it's 1 - 2 Mar, my answer is supposed to display 1 weekend in cell B15 Weekend because the rate follows 1 Mar and since the service stops on 2 Mar, it will be ignored.

    If the condition is not that particular i believe NETWORKDAYS would have solved it, but so happens i'm required to somewhat minus 1 day. But then, minus 1 day does not apply well if lets say the date is between 1 - 7 Mar, where a weekend is involved. In this case, my answer is supposed to display 5 Normal days as the service stops in 7 Mar but the rate follows the rate in 6 Mar.

    Thank you and hope to be able to solve this case.

    -Ichigo

+ 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. Turning weekends into weekdays?
    By bobing in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-16-2013, 06:34 AM
  2. WEEKENDS VS. WEEKDAYS
    By TLAngelo in forum Excel General
    Replies: 0
    Last Post: 07-10-2006, 02:04 PM
  3. WEEKENDS VS. WEEKDAYS
    By TLAngelo in forum Excel General
    Replies: 7
    Last Post: 05-12-2006, 12:35 PM
  4. IF statement with Weekends vs. weekdays
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-07-2006, 04:13 PM
  5. Recognizing weekends and changing to weekdays in Excel
    By hanauer in forum Excel General
    Replies: 2
    Last Post: 01-07-2005, 11:08 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