Hi Anders,

Thank you for the responding to my post.

I see that you relay understood my points, i am happy with that.

My query again was due to mismatch. The formula yous sent was helping but its not giving me the exact hours as I expected. Therefore i sent two spreadsheet:

1. Clock-In and Clock-Out from the Matrix System 

2. Is a spread sheet  I created to check if an employee clock-in 7.5 or less.

From the second spread sheet you will notice that it has 5 column that contains the following;  

Times In, Hours Late, Hours to Be PAID, Total Hours Required, Net Paid Hours, from the Net Paid Hours (NPH) you will notice that it starts docks(cuts) hours at 8:16 am (note that 8 and 16 will be in separate column, but both come under  Time In), hence it will show as; 

8:16 - 7.23

8:17 - 7.22

8:18 - 7.20

8:19 - 7.18 

....and it will continue all the way to 11:00.

Thus i want a Formula that will provide that exact time as what is the second spread sheet.

For those that Clock-Out 40, 30, 20, 15 any minutes early, before 4:30 they hours has to be Docked(cut), because we don't have an arrangement where they can be paid full hours.

With that, I hope, you are clear with my points and I await your kind response.  

Hello Charlie,

Thank you for the additional explanation, I think I have grasped the picture now. As I still assume you need to copy and paste this formula to every new report you export from the time stamp system I have used a formula with a lot of nested IF functions. I normally avoid this kind of solution as these formulas are most of the time difficult to read and understand. But with this layout of the file you have presented there are not much other options available.

The .xls file format only support a limited number (7) of nested functions, while the .xlsx file format supports far more (64). For this formula to work you need to save your file as .xlsx.

Copy below formula and paste it in cell G7 in TIMES.xlsx workbook. Then copy it further to desired cells.

=IF(AND(ISBLANK(C7),ISBLANK(D7)),"",
IF(OR(ISBLANK(C7),ISBLANK(D7)),0,
IF(AND(TIME(HOUR(C7),MINUTE(C7),0)<TIME(8,0,0),TIME(HOUR(D7),MINUTE(D7),0)>=TIME(16,30,0)),7.5,
IF(AND(TIME(HOUR(C7),MINUTE(C7),0)>=TIME(8,0,0),TIME(HOUR(C7),MINUTE(C7),0)<=TIME(8,15,0),TIME(HOUR(D7),MINUTE(D7),0)>=TIME(16,30,0)),(TIME(16,30,0)-TIME(HOUR(C7)+1,MINUTE(C7),0))*24,
IF(AND(TIME(HOUR(C7),MINUTE(C7),0)>=TIME(8,0,0),TIME(HOUR(C7),MINUTE(C7),0)<=TIME(8,15,0),TIME(HOUR(D7),MINUTE(D7),0)<TIME(16,30,0)),(TIME(HOUR(D7),MINUTE(D7),0)-TIME(HOUR(C7)+1,MINUTE(C7),0))*24,
IF(AND(TIME(HOUR(C7),MINUTE(C7),0)>TIME(8,15,0),TIME(HOUR(D7),MINUTE(D7),0)>=TIME(16,30,0)),(TIME(16,30,0)-TIME(HOUR(C7)+1,MINUTE(C7),0))*24-0.2,
(TIME(HOUR(D7),MINUTE(D7),0)-TIME(HOUR(C7)+1,MINUTE(C7),0))*24-0.2))))))

Some brief explanation of above formula.
First IF checks if cells C7 and D7 are blank, if true then put in blank.
Second IF checks if cells C7 or D7 are blank, if true then put in zero (0).
Third IF checks if time value in cell C7 is lower than 8 am and if time value in cell D7 is greater or equal to 4.30 pm, if true then put in 7.5.
Fourth IF checks if time value in cell C7 is between 8 and 8.15 am and time value in cell D7 is greater than or equal to 4.30 pm, if true then calculate time using values from C7 and 4.30 pm without time penalty.
Fifth IF checks the same apart from that it now checks if time value in cell D7 is lower than 4.30 pm, if true calculate time using values in cells C7 and D7.
Sixth IF checks if time value in cell C7 is greater than 8.15 am and if time value in cell D7 is greater than or equal to 4.30 pm, if true then calculate time using values from C7 and 4.30 pm with 0.2 time penalty.
If all above is not true, then calculate time using time values from C7 and D7 with 0.2 time penalty.

I do hope this is what you are after. I also found no use of your Hours Saved workbook, as there is no need to lookup and find values from a different workbook with this kind of data.

I would still urge you to try to find a different export option as it would be much easier for you if you can export data in a tabular format as you can import using Power Query and create nice looking dynamic reports and so forth.

Br,
Anders

Hi Anders,

Thank you very much and appreciate you help.