subtract the time difference from another time difference

[Guest]

How do I subtract the time difference from another time difference?

i.e -

I have 6:34pm (time I left) in G12 and I have 8:58am (time I came in) in
G11. I found the difference between the two times by using formula:
=TEXT(G12-G11,"h:mm"), which gave me 9:36. So this is telling me I worked 9
hours and 36 minutes for that day.

Now I want to calculate how long my lunch break was, since I do not get paid
for it, which is below:

I have 1:44pm (time I came back from lunch) in G15 and I have 1:05pm (time I
left for lunch) in G14. I found the difference between the two times by using
formula: =TEXT(G12-G11,"h:mm"), which gave me 0:39.

Now I know I was at work for 9 hours and 36 minutes, but had a 39 minute
lunch break. What formula could I use to show me exactly how many hours and
minutes I really worked?

The formula should show me that I really worked 8 hours and 57 minutes.

Any ideas??

 

[Guest]

Hi,

Try this:

=TEXT((G14-G11)+(G12-G15),"h:mm")

G11=8:58am
G14=1:05pm
G15=1:44pm
G12=6:34pm

Thanks,

 

[David Biddulph]

Firstly, unless you need the output as text, you don't need the TEXT()
function, so instead of =TEXT(G12-G11,"h:mm") you could just use =G12-G11
and format the cell as h:mm to see 9:36. Similarly your =G15-G14 (which I
assume is what you intended to write as the expression that gives you
0:39?).
You can then just subtract your 0:39 cell from your 9:36 cell. Otherwise
you can do it in one formula by =(G12-G11)-(G15-G14) or =G12+G14-G11-G15.
In each case, format the result as h:mm.
If your numbers mght eventually add up to more than 24 hours, use [h]:mm.
If your times might wrap round across midnight, change the =G12-G11 to
=MOD(G12-G11,1).

 

[Guest]

Farhad, I just want to say Thank You so much, you're amazing!!!!