subtract the time difference from another time difference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dannigirl - 26 Sep 2007 21:46 GMT

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??

Reply to this Message

Farhad - 26 Sep 2007 22:21 GMT

Hi,

Try this:

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

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

Thanks,

Signature

Farhad Hodjat

> How do I subtract the time difference from another time difference?
>
[quoted text clipped - 19 lines]
>
> Any ideas??

Reply to this Message

Dannigirl - 30 Sep 2007 15:47 GMT

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

> Hi,
>
[quoted text clipped - 32 lines]
> >
> > Any ideas??

Reply to this Message

David Biddulph - 26 Sep 2007 23:02 GMT

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).

Signature

David Biddulph

> How do I subtract the time difference from another time difference?
>
[quoted text clipped - 24 lines]
>
> Any ideas??

Reply to this Message