IF - Logical question

[civilojk]

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!

 

[civilojk]

you can click the link to see my spreadsheet

 

[civilojk]

And this is the formula I used for C3

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

 

[thisoldman]

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.

 

[civilojk]

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

 

[thisoldman]

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