Calculation using Time part of date time fields

[macdca]

<TABLE style="WIDTH: 95pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=126 border=0 x:str><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 95pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=126 height=17 x:num="40563.579861111109">20/01/2011 13:55</TD></TR></TBODY></TABLE>

I have a spreadsheet with a date/time column in the above format. I want to match it to another worksheet with a similar column in it and subtract one from the other for matching rows. I have a flag from a look up to match the rows, but I want the date field in one minus the date field in the other to give me hours and minutes, and in a way that I can chart it? I have tried formating to HH:MM but I just get 00:00 in the output?

 

[MisterBate5]

do both your dates have times? Are you looking to find the time difference between the two fields? Or do you want to do the subtraction using only the dates and do something with the remaining time-part?

Would help to see sample data (see my sig for how to post some) and to understand with two real dates/times what you want/expect the result to be...

 

[macdca]

Yes both have dates and times, and the difference might be more than 12 or 24 hours? I would like the results in hours, whether its 26 hours or -26 hours (minutes also)?

 

[macdca]

I have managed to get the calculation to work with a custon format (H) mm.ss but this doesnt work for negative times, as they appear like ###### what can I do for this?
<TABLE style="WIDTH: 75pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=100 border=0 x:str><COLGROUP><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 75pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=100 height=17>DATETIMEDIFF</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="3.4687500003201421E-2">0:49:57</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="2.9432870367600117E-2">0:42:23</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="1.0682870371965691E-2"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="2.9386574075033423E-2">0:42:19</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="1.0578703702776693E-2">0:15:14</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="2.4305555416503921E-4">0:00:21</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="1.8680555556784384E-2">0:26:54</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="1.7546296294312924E-2">0:25:16</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17 x:num="-0.14906250000058208">#############</TD></TR></TBODY></TABLE>

 

[MisterBate5]

Looks like there's no way to show negative elapsed hours. Try instead wrapping ABS() around your formula, then using conditional formatting testing whether your formula returns a negative elapsed and if so, changing the formatting (e.g. showing in RED font)

 

[macdca]

I found this and changing to 1904 dates seems to work

http://www.brainbell.com/tutorials/ms-office/excel/Display_Negative_Time_Values.htm

 

[MisterBate5]

Thx - seems you got to where you wanted to be without much help.