Calculation of Deficit and Excess to Duty Time Using Military Time

lani_berina

New Member
Joined
Dec 13, 2010
Messages
5
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]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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>
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,226,729
Messages
6,192,696
Members
453,747
Latest member
tylerhyatt04

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