Calculation of Deficit and Excess to Duty Time Using Military Time

[lani_berina]

Hi im having difficulties in calcuting this Daily Time Attendance below is the sample of what im trying to do. I am using the fomula of =MOD(TEXT(E8,"00\:00")-TEXT(D8,"00\:00"),1) in Hours Render and it give the right the answer; however there's a problem in calculating the deficit time using this formula =MOD(F8-G8,1) it gives wrong answer 8-8=8 it must be 0. If i enter 0700-1800 there will be an excess of 3:00. Anyone can provide some suggestions and answer to this post. Thank you.[TABLE="width: 543"]
<tbody>[TR]
[TD="colspan: 3"]
[TABLE="width: 543"]
<tbody>[TR]
[TD="colspan: 3"]Aug. 21 – Sept. 20 -2014
[/TD]
[TD]IN
[/TD]
[TD]OUT
[/TD]
[TD]No of Hours Render
[/TD]
[TD]Regular Hours
[/TD]
[TD]Deficit in Duty Time
[/TD]
[TD]Excess to Duty Time
[/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]Day
[/TD]
[TD]Time
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]Thursday
[/TD]
[TD]0700-1500
[/TD]
[TD]07:00
[/TD]
[TD]15:00
[/TD]
[TD]8:00
[/TD]
[TD]08:00
[/TD]
[TD]8:00
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

I get 0:00 with your formula:
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-weight:bold; text-align:right; ">21</td><td >Thursday</td><td >0700-1500</td><td style="text-align:right; ">7:00</td><td style="text-align:right; ">15:00</td><td style="text-align:right; ">8:00</td><td style="text-align:right; ">8:00</td><td style="text-align:right; ">0:00</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H8</td><td >=MOD(F8-G8,1)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

 

[Scott Huish]

Responding to your PM. The 1900 date system (the default for PC) doesn't support negative time.

Unless you switch to the 1904 date system, you will need to display the answer as text. Perhaps this formula will work for you:

=IF(F8-G8<0,"-","")&TEXT(ABS(F8-G8),"hh:mm")