Tracking when someone starts going into LWOP

[PortlandBound]

Hi all,

I created this rather inelegant tracking spreadsheet to show when employees have taken sick leave (SL) or vacation leave (VA) throughout a month.

With some simple formulas and conditional formatting, it does the vast majority of what I need it to do (displays all employee's starting SL and VA, shows the SL and VA used during the month, and how much leave they have now (or at the end of the month)). However, one of the problems I've encountered is not knowing the date that someone goes into LWOP. Currently I can see that someone has gone into LWOP based upon the conditional formatting of a white background and red text, with a negative number, but not when it started.

So, to get to my question:

Is there a way to make that happen? I was thinking if there is a way to make any of the values in J1 through AN1, or AO1 through BS1 appear in the E and I columns, respectively, once the D or H columns go into the negatives, that would be a simple/crude way of doing it.

(Also, I would not be opposed to suggestions or points in the right direction on more concise and elegant methods of tracking this kind of information.)


Thank you!

[PortlandBound]

Okay, no one took a bite so I decided to go back to the drawing board and refine things a bit. I've attached a much more compact and (I think, anyway) improved spreadsheet here. (I've only included 3 names but please keep in mind that this is for the purpose of tracking leave usage/exhaustion for nearly 300 staff.) However, my problem remains the same.

I'm still being asked to have the date in which LWOP first begins (and then going forward) to be easily identified by changing the fill color of rows C5, C12, C19, etc., once more names are added. FYI, these rows denote work days (light aqua fill) and Regular Days Off (RDO; bold black text in a yellow fill).

I can think of ways to format the rows, but everything I come up with just formats the entire row as soon as B8 and B11 equal less than zero, whereas I want the condition to only apply to the date where the balance first goes into negative, and then going forward.

For example:

Adam Smith is in LWOP as of 11/26/14, based on exceeding his 120 hours of VA. Is there a formula I can use to conditionally format C5:AG5 so that once his SL or VA balance goes into the negative (in this case on the 26th) that cell (AB5) and every cell going forward (AC5:AG5), so long as the relevant leave balance(s) remain negative, changes its fill color to bright red without changing the formatting of C5:AA5?