Calculating difference between two times

Bangaz

New Member
Hi Guys

I am doing a timesheet for our engineers at work

Does anyone know a formula to calculate the difference between two times. Please see below

<TABLE style="WIDTH: 608pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=816 x:str><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" span=15 width=51><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><TBODY><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; WIDTH: 266pt; HEIGHT: 17.1pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1.5pt solid" class=xl42 height=22 width=357 colSpan=7>Dayshift</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 228pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1.5pt solid" class=xl34 width=306 colSpan=6>Nightshift</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #ffff99; WIDTH: 114pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1.5pt solid" class=xl43 rowSpan=2 width=153 colSpan=3>Total Actual Hours</TD></TR><TR style="HEIGHT: 17.1pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 17.1pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33 height=22> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1.5pt solid" class=xl39 colSpan=3>From</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1.5pt solid" class=xl41 colSpan=3>To</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1.5pt solid" class=xl37 colSpan=3>From</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1.5pt solid" class=xl37 colSpan=3>To</TD></TR><TR style="HEIGHT: 27pt; mso-height-source: userset" height=36><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #ccffcc; HEIGHT: 27pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 height=36>Actual</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1.5pt solid" class=xl45 colSpan=3 x:num="0.58333333333333337">14:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1.5pt solid" class=xl46 colSpan=3 x:num="0.70833333333333337">17:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1.5pt solid" class=xl46 colSpan=3 x:num="0.91666666666666663">22:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1.5pt solid" class=xl46 colSpan=3 x:num="0.25">06:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1.5pt solid" class=xl38 colSpan=3> </TD></TR></TBODY></TABLE>

So for this day the engineer has worked 3hrs during the day and 8hrs during the night.

I need a formula that will add the dayshift and night shift hours together.

Thanks

Bangaz

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

VoG

Legend
Try this

Excel Workbook
ABCDEF
1Actual14:0017:0022:0006:0011:00
Sheet3

Bangaz

New Member
Thank you - That works great.

Never used MOD before so will look it up to see what it means.

Replies
1
Views
443
Replies
5
Views
971
Replies
13
Views
1K
Replies
12
Views
428
Replies
3
Views
353

1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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.

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

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