Calculating Lost TIme

[WxShady13]

Good morning I have a strange request. I am working on calculating if an employee missed any time during the previous month. I have it written to a table and am using Conditional Formatting to highlight any value greater than 0 (That would indicate lost time). If in any month prior to the current month the employee has a 0 I need to add 3 (example....If on 3/1/19 an employee has two 0 for Jan and Feb then they would have 6 in accrued hours). If the cell is greater than 0 then they get 0. I am stuck on the IF portion of the formula because I do not know how to get it to only calculate the 0 for previous months. Any help would be appreciated...

 

[James006]

Hi,

Why don't you post your current formula ... :wink:

 

[WxShady13]

=SUMIF(F2:Q2,">=$AJ$2")

What this does is look for greater than 0 in F to Q. F(Jan) and Q(Dec) are in Row 1.

 

[James006]

OK ...

But if your 12 months do span from F to Q ...

How do you plan to adjust this range ...?

 

[WxShady13]

This is where I am ultimately stuck. I know I can pull a current month and even get it to match the Jan format, but I do not know how to get it to take current date, and only add from the previous months if the cell contains a 0.

 

[James006]

You can use Column() ... which in you case will start with 6 (Column F ) till 17 (Column Q) ...

HTH

 

[WxShady13]

So I would use something like a SUMIF Column 6 thru Column 17 if they are less than my Current Month column? Sorry I am still confused.