Adding and Subtracting date and time

[Miko K]

Hi everyone. I have looked all over and I hope I am not double posting. I've tried a number of examples but I can't seem to get it to work. I need help with the following problem, with the desired results in column C. Thanks so much.

<TABLE style="WIDTH: 243pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=323><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17 width=118>10/17/11 12:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=112>10/17/11 1:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=93>1:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>10/17/11 12:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>10/17/11 9:00 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>(3:00)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>10/17/11 12:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>10/16/11 9:00 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>(27:00)</TD></TR></TBODY></TABLE>

 

[vds1]

Try this,

Insert the formula in cell C2

= B2-A2

Then choose column C > Right Click > Format Cells> Choose Time > Select the first option in the list.

Hope it helps!

 

[Miko K]

Try this,

Insert the formula in cell C2



Then choose column C > Right Click > Format Cells> Choose Time > Select the first option in the list.

Hope it helps!

Hi sorry I get the pesky #### when I need a 'negative' time
. Column C is "hours" only (easy fix)

<TABLE style="WIDTH: 299pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=397><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 89pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=17 width=118>10/17/11 12:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=112>10/17/11 1:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 width=93>1:00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 width=74 align=right>1:00:00 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=17>10/17/11 12:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>10/17/11 9:00 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>(3:00)</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 align=middle>#########</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=17>10/17/11 12:00 PM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>10/16/11 9:00 AM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>(27:00)</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 align=middle>#########</TD></TR></TBODY></TABLE>

 

[vaskov17]

make the column wider to fix "#######" issue

 

[Miko K]

make the column wider to fix "#######" issue

The column does not fix because it is a negative time.. the ### is an error not a cell width problem. It actually shrinks the column narrower when I do it.