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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

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