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 -->[TABLE="width: 516"]
<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>[TR]
[TD="width: 70"]Emp No[/TD]
[TD="width: 203"]Name[/TD]
[TD="width: 105"]Designation[/TD]
[TD="width: 57"]Grade[/TD]
[TD="width: 81"]In Time[/TD]
[TD="width: 82"]Out Time[/TD]
[TD="width: 31"]OT[/TD]
[TD="width: 57"]Late[/TD]
[/TR]
[TR]
[TD]00059957[/TD]
[TD]Mohsin [/TD]
[TD]Line Leader[/TD]
[TD]4[/TD]
[TD]07:55:05 AM[/TD]
[TD="align: right"]8:59:59 PM[/TD]
[TD]4[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]00075323[/TD]
[TD]MD.Manik Miah [/TD]
[TD]Line Leader[/TD]
[TD]4[/TD]
[TD]07:47:23 AM[/TD]
[TD="align: right"]9:00:33 PM[/TD]
[TD]4[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]00075567[/TD]
[TD]Humayun Kabir Shahin [/TD]
[TD]Line Leader[/TD]
[TD]4[/TD]
[TD]07:38:03 AM[/TD]
[TD="align: right"]8:47:21 PM[/TD]
[TD]4[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]00085901[/TD]
[TD]MD.Yousuf Uddin [/TD]
[TD]Line Leader[/TD]
[TD]4[/TD]
[TD]07:53:57 AM[/TD]
[TD="align: right"]9:00:36 PM[/TD]
[TD]4[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]00094029[/TD]
[TD]Sanaya Akter [/TD]
[TD]Line Leader[/TD]
[TD]4[/TD]
[TD]07:50:34 AM[/TD]
[TD="align: right"]8:59:15 PM[/TD]
[TD]4[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]00099581[/TD]
[TD]Md Rezaul Karim[/TD]
[TD]Line Leader[/TD]
[TD]4[/TD]
[TD]07:41:12 AM[/TD]
[TD="align: right"]9:00:43 PM[/TD]
[TD]4[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]00000037[/TD]
[TD]Md. Shajahan[/TD]
[TD]Checker (Store)[/TD]
[TD]4[/TD]
[TD]07:39:21 AM[/TD]
[TD="align: right"]8:57:16 PM[/TD]
[TD]4[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]00048613[/TD]
[TD]Sayed Badrul Hasan[/TD]
[TD]Cleark(Store)[/TD]
[TD]3[/TD]
[TD]07:47:41 AM[/TD]
[TD="align: right"]8:55:35 PM[/TD]
[TD]4[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]00052623[/TD]
[TD]Md Jahed Hossain [/TD]
[TD]Cleark(Store)[/TD]
[TD]3[/TD]
[TD]07:51:42 AM[/TD]
[TD="align: right"]8:56:00 PM[/TD]
[TD]4[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]00073880[/TD]
[TD]Abu Rashed Md. Minhaj Uddin [/TD]
[TD]Checker[/TD]
[TD]4[/TD]
[TD]08:05:38 AM[/TD]
[TD="align: right"]8:57:22 PM[/TD]
[TD]4[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]00027346[/TD]
[TD]Shadona Rani Somador[/TD]
[TD]Checker[/TD]
[TD]4[/TD]
[TD]07:47:00 AM[/TD]
[TD="align: right"]9:02:25 PM[/TD]
[TD]4[/TD]
[TD]00:00:00[/TD]
[/TR]
</tbody>[/TABLE]

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)