IF - Logical question

civilojk

New Member
Joined
May 18, 2018
Messages
4
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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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" )))
 
Upvote 0
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.

Y0PioA7.png
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top