Calculating Lost TIme

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
185
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi,

Why don't you post your current formula ... :wink:
 
Upvote 0
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.
 
Upvote 0
OK ...

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

How do you plan to adjust this range ...?
 
Upvote 0
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.
 
Upvote 0
You can use Column() ... which in you case will start with 6 (Column F ) till 17 (Column Q) ...

HTH
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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