Date and time calculation

[Lyn Radnell]

can someone please help me, not that good on Excel

I want to calculate the difference in hours between dates and time. for example

Date is 05/09/2011 Time is 14:00

Date is 07/09/2011 Time is 09:15

Can anyone please tell me the formula to work out the hour difference between the 2 dates/times

THANK YOU IN ADVANCE

Lyn

 

[rockyw]

=(B1-A1)*24 will work. I have first time and date in A, second time and date in B, this formula in C. Works well.

 

[Cindy Ellis]

Hello Lyn, and welcome to the forum!
The key is in the format..
Assume Date/time 1 is in cell A2, and date/time 2 (the later of the 2 dates) is in cell B2.
In cell C2, enter

=B2 - A2

Then...format the cell as

[h]:mm:ss

.
If you're using Excel 2007, find the "number" group on the Home tab. Where it says "General", click to expand the list box and choose "More number formats" at the very bottom of the drop-down list. Choose "Custom" in the left panel, and choose (if available) or enter the format shown above.
Hope that helps,
Cindy

 

[Lyn Radnell]

Hi Cindy,

Thanks so much for your response. I have tried it but it seems to ignore the date, see below:

<TABLE style="WIDTH: 358pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=476><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6115" width=172><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 6912" width=194><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3925" width=110><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 129pt; HEIGHT: 14.4pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19 width=172 align=right>22/08/2011 14:23</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 146pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=194 align=right>24/08/2011 14:25</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=110 align=right>0:02</TD></TR></TBODY></TABLE>

What am I doing wrong?

Thanks again from down under.

Lyn

 

[Cindy Ellis]

Hi Lyn,
With built-in date/time formats that are different for various parts of the world, I'm not sure I can be much more help...Maybe someone using the same regional edition of Excel can give a better answer?
When I change the date/time format in Excel to English (Australia), I get the following:

<TABLE style="WIDTH: 311pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=415 border=0><COLGROUP><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6253" width=171><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5961" width=163><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=171 height=20>22/08/2011 14:23</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 122pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=163>24/08/2011 14:25</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=81>48:02:00</TD></TR></TBODY></TABLE>

Sorry I wasn't able to be more help,
Cindy

 

[rockyw]

Lyn, The formula I posted work if you set your cell format correct. Put in A3 =(B1-A1)*24 Set the format in A1 and B1 to Custom m/d/yy:h:mm@ Set the format in B3 as number, 2 decimal places. I entered these numbers and got this result.

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=319 border=0><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=117 height=20>8/20/2011 14:25</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 78pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=104>8/24/11 14:25</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=98>96.00</TD></TR></TBODY></TABLE>
Hope this helps.

 

[Lyn Radnell]

Hi Rocky and Cindy,

Thanks so much for your help. They both WORK!! thanks you

Thanks

Lyn

 

[Cindy Ellis]

Thanks for the feedback...glad it worked

 

[rockyw]

Your welcome Lyn, have a good week. Rocky