Calculating Lost TIme

Board Regular
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...

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi,

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

Code:
``=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.

OK ...

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

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

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.

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

HTH

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.

Replies
2
Views
86
Replies
13
Views
387
Replies
1
Views
313
Replies
1
Views
183
Replies
17
Views
384

1,196,508
Messages
6,015,615
Members
441,906
Latest member
gafoor

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.

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

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