+ Reply to Thread
Results 1 to 3 of 3

Problem making a spreadsheet schedule based on the availability of two items.

  1. #1
    Registered User
    Join Date
    04-25-2019
    Location
    Columbus, Ohio, USA
    MS-Off Ver
    2010
    Posts
    2

    Problem making a spreadsheet schedule based on the availability of two items.

    For some reason, the logic behind the spreadsheet schedule I am making is very confusing to translate to the spreadsheet. I am going to be servicing computers for about a hundred users, while their computers are being serviced, they will be borrowing one of two loaner computers. The normal stretch for servicing the computers is three days, but it could be shorter or longer. The two loaner computers get loaned out based on what loaner computer is available when that user brings in their computer.

    I am making a schedule that it like the following:

    Col A: User Name
    Col B: Date user is to bring in their computer
    Col C: Date user is intended to pick up their computer
    Col D: What loaner computer in to be given to that user to use in the meantime (Loaner 1 or Loaner 2)

    Say the list look like this

    Row 1: Johnny Apple, 05/01/2019, 05/03/2019, Loaner 1
    Row 2: Kevin Bicycle, 05/01/2019, 05/03/2019, Loaner 2
    Row 3: Larry Cat, 05/04/2019, 05/07/2019, Loaner 1
    Row 4: Marvin Dot, 05/04/2019, 05/07/2019, Loaner 2
    Row 5: Nancy Egg, 05/08/2019, 05/11/2019, Loaner 1
    Row 6: Ollie Fawn, 05/08/2019, 05/11/2019, Loaner 2
    Row 7: Pat Green, 05/12/2019, 05/15/2019, Loaner 1
    Row 8: Randy Holly, 05/12/2019, 05/15/2019, Loaner 2

    Now, without any needed alterations to the schedule, the schedule seems obvious and predictable. However, I have to allow for a situation like, say, Larry Cat's computer actually ends up taking 7 days to service, so the full three days are extended by four. That means that the following alternation to the schedule would automatically recalculate to:

    Row 1: Johnny Apple, 05/01/2019, 05/03/2019, Loaner 1
    Row 2: Kevin Bicycle, 05/01/2019, 05/03/2019, Loaner 2
    Row 3: Larry Cat, 05/04/2019, 05/11/2019, Loaner 1
    Row 4: Marvin Dot, 05/04/2019, 05/07/2019, Loaner 2
    Row 5: Nancy Egg, 05/08/2019, 05/11/2019, Loaner 2
    Row 6: Ollie Fawn, 05/12/2019, 05/15/2019, Loaner 1
    Row 7: Pat Green, 05/12/2019, 05/15/2019, Loaner 2
    Row 8: Randy Holly, 05/16/2019, 05/19/2019, Loaner 1

    Can someone help this novice work out this logic is Excel? I cannot seem to work out how I would get a spreadsheet to work out this logic?


    Timothy

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Problem making a spreadsheet schedule based on the availability of two items.

    Hello timothysklugh and Welcome to Excel Forum.
    Perhaps the following will help.
    The first two dates when the client is expected to bring their computer in are manually filled then the following formula fills rows 4 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The date on which the computer is expected to go back out to the client is filled using: =MIN(D2,SUM(B2,3))
    The actual dates on which the computer goes back out to the client need to be manually filled.
    The first two rows of loaner computer need to be manually filled then, starting with row 4, use: =IFERROR(INDEX(E$2:E3,MATCH(ROWS(A$1:A1),F$2:F3,0)),"")
    The Rank column, which may be moved and/or hidden for aesthetic purposes uses:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us 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.

  3. #3
    Registered User
    Join Date
    04-25-2019
    Location
    Columbus, Ohio, USA
    MS-Off Ver
    2010
    Posts
    2

    Re: Problem making a spreadsheet schedule based on the availability of two items.

    Thank you for responding! I will take a look.


    Timothy

+ 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. Creating an availability based match schedule for 20 games.
    By CheesySloth in forum Excel General
    Replies: 1
    Last Post: 03-03-2019, 02:55 AM
  2. Determining Staffing Pars based on Availability of schedule
    By njm0059 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-20-2015, 12:13 AM
  3. [SOLVED] Need help in how to add or remove items based on availability
    By migdad in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-06-2014, 12:05 AM
  4. availability based on schedules spreadsheet
    By paramatman13 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-02-2013, 05:48 PM
  5. Replies: 2
    Last Post: 07-25-2012, 02:19 AM
  6. Figuring out Availability based upon work/task schedule
    By B. Baumgartner in forum Excel General
    Replies: 0
    Last Post: 05-03-2007, 10:58 AM
  7. Replies: 1
    Last Post: 04-09-2006, 05:25 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