How do I total up a Military time card including deducting lunch?

[RudeBoy]

My cells are:
Time In
Out To Lunch
In From Lunch
Time Out

For Military format I am using 00\;00

Helpppppppppppppppp

 

[RudeBoy]

Ok I am getting CLOSER
I used this to get the Total for the Military time but I still need to take the hour off for lunch =J20-G20+(G20>J20)

G20=08:00 H20=13:00 I20=14:00 J20=17:00 so the formula calculated Military time & came up with 9 hrs which is correct. So now I need to somehow get the Lunch in there with the H20 & I20. Helpppppppppppppppp Thanks Take Care

 

[Tetra201]

Try this:

=MOD(J20-I20,1)+MOD(H20-G20,1)

 

[Logit]

.
Or this :



[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

Time In​

[/TD]
[TD]

Out Lunch​

[/TD]
[TD]

In Lunch​

[/TD]
[TD]

Time Out​

[/TD]
[TD][/TD]
[TD]

Total Hrs​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

8:00​

[/TD]
[TD]

12:00​

[/TD]
[TD]

12:30​

[/TD]
[TD]

16:30​

[/TD]
[TD]

=​

[/TD]
[TD]

8:00​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

8:15​

[/TD]
[TD]

12:10​

[/TD]
[TD]

12:52​

[/TD]
[TD]

17:34​

[/TD]
[TD]

=​

[/TD]
[TD]

8:37​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

9:10​

[/TD]
[TD]

11:45​

[/TD]
[TD]

12:26​

[/TD]
[TD]

16:15​

[/TD]
[TD]

=​

[/TD]
[TD]

6:24​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

=​

[/TD]
[TD]

0:00​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

=​

[/TD]
[TD]

0:00​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

In F17 :​

[/TD]
[TD]=SUM(D17-A17)-(C17-B17)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Formula:

=SUM(D17-A17)-(C17-B17)

Edit ranges as required.

 

[RudeBoy]

This Worked Thanks =MOD(J20-I20,1)+MOD(H20-G20,1)
Take Care