Calculate time

Meelkoppie

New Member
Joined
Oct 17, 2008
Messages
38
Hi, can someone please help me with a formula to calculate time. I created a formula, but the problem is, it also calculated duplicate time. For example:

John's Overtime for task A: 19h05 to 21h15 = 2 hours and 10 min
John's Overtime for task B: 20h00 to 21h30 = 1 hours and 30min

Total hours = 3 hours and 40 min which is incorrect. It must exclude part time for task B, because it overlap with task A. The correct hours must be 2 hours and 25 min.

I will appreciate it very much if someone can help me.

Thank you very much.

Meelkoppie
 

Some videos you may like

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.

Ormus

Board Regular
Joined
Nov 1, 2011
Messages
92
Colomn A has the start time (format hh:mm)
Colomn B has the end time (format hh:mm)

Colomn C and copy down, place: =IF(A2>B1,B2-A2,B2-B1). This will give 02:10 for the first row and 00:15 for the 2nd row. Sum of the 2 gets to 02:25
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

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
Top