+ Reply to Thread
Results 1 to 3 of 3

Trying to display a name in a calendar if several criteria are met (edited)

  1. #1
    Registered User
    Join Date
    08-15-2018
    Location
    Portland, OR
    MS-Off Ver
    '13
    Posts
    16

    Red face Trying to display a name in a calendar if several criteria are met (edited)

    Hello all, I've learned alot from this forum over the last year but I have one that I can't seem to jiggle into working and wanted to see if I can get some insight. I'm making a sharepoint facing Time-Off calendar and am almost done but I'm at the point where I'm trying to figure out how to structure displaying the names that get approved off, under the dates they were approved for. Part of me feels like it has something to do with the way we count between days vs how many days.

    For example, if you have the 1st through the 10th off, you could =DAYS this, however, the very first day is not counted. It would result in 9, when in fact, you have 10 days off, 1-10. So when I tell a calendar date formula to display the following formula:

    Please Login or Register  to view this content.
    The 'greater than or equal to' or 'less than or equal to' isn't registering correctly. I THINK, that's just a hypothesis.

    If anyone can take a look and give me some direction, that would be fantastic. In my attached sheet, two examples are actually showing correctly for their respective dates, and I can't figure out why those happen to show correctly, while the others do not.
    Attached Files Attached Files
    Last edited by PetGiraffe; 11-15-2019 at 01:13 PM. Reason: My post title did not convey enough information

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,040

    Re: Trying to display a name in a calendar if several criteria are met (edited)

    For a the latest Sup name based on your criteria: =IFERROR(LOOKUP(2,1/(P9>$V$14:$V$136)/(P9<$W$14:$W$136)/($Y$14:$Y$136="APPROVED")/($Z$14:$Z$136="O"),$S$14:$S$136),"")

    For multiple results for same data, an array formula entered with Ctrl+Shift+Enter. But this will may slow your file. Wrap it in a iferror formula as well:
    =IFERROR(LOOKUP(2,1/(Q9>$V$14:$V$136)/(Q9<$W$14:$W$136)/($Y$14:$Y$136="APPROVED")/($Z$14:$Z$136="O"),$S$14:$S$136),"")
    Click the * to say thanks.

  3. #3
    Registered User
    Join Date
    08-15-2018
    Location
    Portland, OR
    MS-Off Ver
    '13
    Posts
    16

    Re: Trying to display a name in a calendar if several criteria are met (edited)

    That took care of it! Thank you so much!

+ 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. Calendar - NEED IT TO BE 7 WORK DAY WEEKS
    By icebeshining in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-12-2017, 12:26 PM
  2. how does this calendar work?
    By mchan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-24-2015, 12:47 PM
  3. Rolling Calendar array will not work for a completely horizontal calendar
    By Michaelwk10 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-11-2014, 05:11 PM
  4. Calendar does not work
    By Oeysbrei in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-25-2012, 04:06 PM
  5. Calendar functions - linking work activities with formatted dates to calendar
    By SKSS in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 05-14-2012, 06:38 PM
  6. How do I create work day only calendar dates?
    By rjmckay in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-23-2011, 05:44 PM
  7. Cannot get calendar to work on more than 2 columns
    By unley in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2010, 08:49 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