time diference between 2 cells

[paul w]

time difference problem
im trying to calculate the time difference between 2 cells and in the 3rd cell, display the answer in decimal format,
e.g A1 0800 B1 0930 i would like C1 to display 1.5 not 130

 

[paul w]

once again thankyou for you help.
paul w

 

[jasonb75]

that worked thanks, sorry but is there any way of just entering 0800 and having excel change it to 08:00 i have to add a lot of times on my time sheet,

A couple of ways around this if, for any reason, VoG's suggestion doesn't work for what you need.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">800</TD><TD style="TEXT-ALIGN: right">930</TD><TD style="TEXT-ALIGN: right">1.5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">1.5</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C1</TD><TD>=(SUBSTITUTE(B1,RIGHT(B1,2),":"&RIGHT(B1,2))-SUBSTITUTE(A1,RIGHT(A1,2),":"&RIGHT(A1,2))+(A1>B1))*24</TD></TR><TR><TD>C2</TD><TD>=(INT(B1/100)+(MOD(B1,100)/60))-(INT(A1/100)+(MOD(A1,100)/60))+((A1>B1)*24)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

The 'Substitute' formula (in C1 above) will not work if either of the times is between 0000 and 0059.