+ Reply to Thread
Results 1 to 4 of 4

Return a variable based on a selection between dates and times in a range

  1. #1
    Registered User
    Join Date
    02-19-2013
    Location
    West Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Return a variable based on a selection between dates and times in a range

    I am new to the forum and was hoping that someone on here could help me with this problem.

    I am working on a scheduling file that needs to compare the time and date of a call, to the scheduled times of the FTE and return the proper FTE's name based on Unit assignment.

    I want to select "Unit 1" and return the result of the full time equivalent scheduled at that specific date and time. The spreadsheet needs to automatically adjust as new FTEs are scheduled to the same unit at a different time, but on the same date. I have tried to use various VLookup formulas, but keep coming up short.

    For example, a call comes in at 08:00 and unit "1" is selected. The cell needs to return the FTE assigned to that unit between 07:00 and 15:00.
    A new shift activation for Unit 1 would be at 15:01 on the same day, and when a call comes in at 16:00 when the user selects unit "1" the new FTE shows in the cell.

    I have attached a spreadsheet to better explain.

    Thanks,
    Mr. H2
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,138

    Re: Return a variable based on a selection between dates and times in a range

    hi Mr. H2, welcome to the forum. your cells in "FTE Activation" C4:C5 is somehow not captured as a Date. copy the cells from C3 & paste it below. if you realise, C3 in the formula bar reads:
    20/2/2013 (or maybe in your region settings 2/20/2013)
    but C4 & C5 are plain texts:
    February 20,2013

    the latter is not recognised in formulas. with that, use this array formula in "Deployment" sheet I3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it. then copy down & across

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    02-19-2013
    Location
    West Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Return a variable based on a selection between dates and times in a range

    WOW! Thank You! I'm going to try and enter it in my master document now.

  4. #4
    Registered User
    Join Date
    02-19-2013
    Location
    West Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Return a variable based on a selection between dates and times in a range

    Here is the file you requested.

    Thanks,
    Mr. H2
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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