Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtract the time difference from another time difference
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?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtract the time difference from another time difference
Hi,
Try this: =TEXT((G14-G11)+(G12-G15),"h:mm") G11=8:58am G14=1:05pm G15=1:44pm G12=6:34pm Thanks, -- Farhad Hodjat "Dannigirl" wrote: 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?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtract the time difference from another time difference
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). -- David Biddulph "Dannigirl" wrote in message ... 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?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
subtract the time difference from another time difference
Farhad, I just want to say Thank You so much, you're amazing!!!!
"Farhad" wrote: Hi, Try this: =TEXT((G14-G11)+(G12-G15),"h:mm") G11=8:58am G14=1:05pm G15=1:44pm G12=6:34pm Thanks, -- Farhad Hodjat "Dannigirl" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time difference | Excel Discussion (Misc queries) | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
Negative time should be allowed in Excel, eg time difference | Excel Discussion (Misc queries) | |||
Time zone Time difference | Excel Worksheet Functions | |||
Time / Formula to look at time difference | Excel Worksheet Functions |