Delete 2 hour from out time

[Salehin31]

Hi Friends,

Everytime when I face any problem in excel this forum help me a lot.

here my management take decision to minus 2 hour OT (Over Time) from every worker. When I minus their OT the out time will be decrease also e.i. 9:00:00 PM will be 7:00:00 PM. Here I give a list for your kind consideration.
<!-- x-tinymce/html -->

Emp No	Name	Designation	Grade	In Time	Out Time	OT	Late
00059957	Mohsin	Line Leader	4	07:55:05 AM	8:59:59 PM	4	00:00:00
00075323	MD.Manik Miah	Line Leader	4	07:47:23 AM	9:00:33 PM	4	00:00:00
00075567	Humayun Kabir Shahin	Line Leader	4	07:38:03 AM	8:47:21 PM	4	00:00:00
00085901	MD.Yousuf Uddin	Line Leader	4	07:53:57 AM	9:00:36 PM	4	00:00:00
00094029	Sanaya Akter	Line Leader	4	07:50:34 AM	8:59:15 PM	4	00:00:00
00099581	Md Rezaul Karim	Line Leader	4	07:41:12 AM	9:00:43 PM	4	00:00:00
00000037	Md. Shajahan	Checker (Store)	4	07:39:21 AM	8:57:16 PM	4	00:00:00
00048613	Sayed Badrul Hasan	Cleark(Store)	3	07:47:41 AM	8:55:35 PM	4	00:00:00
00052623	Md Jahed Hossain	Cleark(Store)	3	07:51:42 AM	8:56:00 PM	4	00:00:00
00073880	Abu Rashed Md. Minhaj Uddin	Checker	4	08:05:38 AM	8:57:22 PM	4	00:00:00
00027346	Shadona Rani Somador	Checker	4	07:47:00 AM	9:02:25 PM	4	00:00:00

<colgroup><col style="width: 53pt;" width="70"> <col style="width: 152pt;" width="203"> <col style="width: 79pt;" width="105"> <col style="width: 43pt;" width="57"> <col style="width: 61pt;" width="81"> <col style="width: 62pt;" width="82"> <col style="width: 23pt;" width="31"> <col style="width: 43pt;" width="57"></colgroup><tbody>
</tbody>

Thank's in advance

 

[tyija1995]

If you are subtracting 2 hours from a time you can do -2/24 (-1/12 simplified).
So if cell A1 = 20:59:59 you can put in B1:
=A1-1/12
Result is 18:59:59

Is this what you want?

 

[Salehin31]

Thanks for your reply can you make it more easy??

 

[tyija1995]

There might be other ways but this is the easiest way I can think of doing it, someone else may post something that makes more sense though

Time is stored as a number basically between 0 and 1, so midday 12:00:00 is halfway through which is 0.5 (1/2)

Taking off 2 hours is 2 in 24 of a day so that is why you subtract 2/24 (which is equivalent to 1/12)