Results 1 to 6 of 6

Thread: IF - Logical question
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF - Logical question

    https://imgur.com/wEYK5Fs


    I am working on IF Function to get the value so I can use the value to set the conditional formatting. This uses two cells in order to get the values that I need.

    V= Vacant, F = Filled , FG = Filled gain

    Column A, B shows the current employee who is filling the position and Column AD to AD will show projecting employees who will come in and fill the data.

    The issue is, if I have values for someone filling in current position and projeciting personnel information, then somehow projecting personnel data will overwrite current employee's data and it makes the current fill data as vacant even if I have someone in the position.

    Below is the formula I am using and the picture displays how it looks with current formula (top) and how I want it to look (bottom).

    If I have some in f the current fill, then I need it to show as "F" until their loss data and I need "FG" to show according to the gain date if I have projecting employee.

    Maybe my explanation is not clear, if so please reach out so I can give you the better explanation.. I appreciate your assistance in advance!

  2. #2
    New Member
    Join Date
    May 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF - Logical question

    you can click the link to see my spreadsheet

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF - Logical question

    And this is the formula I used for C3

    =IF($AC3="VACANT", IF($B3="VACANT", "V", IF($B3=C$1, ISBLANK($B3)),"F", IF($AC3<=C$1, "FG", "V" )))

  4. #4
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: IF - Logical question

    Using true dates in C1:AA1 made the formula much easier for me to write.

    In C1, enter 1-Sep-2017.
    Right-click-grab the small square drag handle at the lower-right of C1 and drag through to AA1.
    A context menu will pop up. Select "Fill Months".

    The cells from C1 through AA1 should now have the first day of each consecutive month, from 2017-09-01 through 2019-09-01. Change the display of these dates by using the custom number formatting mmm yyyy, to hide the day of the month.

    In C2, enter

    =IF(AND(ISNUMBER($B2),C$1<=EOMONTH($B2,0)), "F", IF(AND(ISNUMBER($AC2), C$1>=$AC2), "FG", "V"))

    Then drag the formula down and to the right.

    I've used blue and red as the two fill colors; red-green color blindness is common. I used blue as the fill color for all the cells, then used conditional formatting to fill the cells containing "V" with light red.


  5. #5
    New Member
    Join Date
    May 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF - Logical question

    @thisoldman, Thank you!!!! it works!!!! I really appreciate it!!

  6. #6
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: IF - Logical question

    I'm glad to have helped. Thank you for posting back with your results.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •