+ Reply to Thread
Results 1 to 5 of 5

How to calculate task schedule based on 4-days work and 4-days OFF including weekends

  1. #1
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Question How to calculate task schedule based on 4-days work and 4-days OFF including weekends

    Dear Experts,

    I'm able to do formula for the straight dates, now i need to have condition that if the 4-days/32hrs of scheduling is done, the next task should take the scheduling date after 4-off days.

    I've updated the attached excel sheet to show sample data and expected outcome for reference to my problem.

    I want to calculate the dates based on the hours scheduled daily. 8-hrs worked is considered 1-day, after completing 4-working days an employee will have 4-days off in the schedule and the next schedule date should be after the 4-off days/32hrs.

    Please i need help in this regards, Thanks!
    Attached Files Attached Files
    Last edited by qadeerume; 07-26-2020 at 07:34 AM. Reason: Elaborated the problem and updated the Excel sheet

  2. #2
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: How to calculate task schedule based on 4-days work and 4-days OFF including weekends

    I updated the excel .. anyone please help!

  3. #3
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: How to calculate task schedule based on 4-days work and 4-days OFF including weekends

    I added S6:U20 to test our my proposed solution. It matches your expected results but it could use some more comprehensive testing!

    In T7 copied down to T11:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The first part of the expression sums the hours up to the previous row and allows 8 hours per day. The second part of the expression adds 4 days every 32 hours.

    Then in U6 copied down to U11 and almost but not quite identical formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The "-0.00001" accommodates an 8 hour day being finished on the the same day rather than spilling into the following day.

    The S13:U20 test case uses the same formulas, but note that that can't simply be copy/pasted.

    I have attached an update with the above

    Let us know if this works for you.
    Attached Files Attached Files
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  4. #4
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: How to calculate task schedule based on 4-days work and 4-days OFF including weekends

    Thank you GeoffW283, it works as intended, i tried all the test cases and its working perfectly. Thanks once again for your support!

  5. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: How to calculate task schedule based on 4-days work and 4-days OFF including weekends

    No problem, glad to help and thanks for the reputation points!

+ 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 number of days including both days
    By dineshiam in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-18-2020, 11:03 AM
  2. Business Days in a Month Excluding Holidays, Including Weekends
    By jenniescharms in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-25-2019, 10:42 AM
  3. [SOLVED] calculate total number of days between two days based on 365 days year
    By aparunkumar in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-14-2016, 12:51 AM
  4. Distributing work across the calendar days based on effort taken per task
    By sriramdh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2014, 02:11 AM
  5. Work Schedule that calculates hours, lunches, days off and vacation days
    By tameronstarr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2014, 12:06 PM
  6. Need a running total of calendar days (including weekends and holidays)
    By armyav09 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-30-2012, 04:14 PM
  7. Replies: 3
    Last Post: 03-31-2008, 01:27 PM

Tags for this Thread

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