Excel help please

gallygirl

Is it possible to calculate the total number of hours between two dates, say 09:00 on 3/1/15 to 06:20 on 5/1/15? I know how to calculate as long as it's within the same day but the formulae only work up to 24 hours.

Thanks

warwicktiger

you can take one date away from another, leaving the answer in days then multiply by 24, and add the "odd hours"

SnowMan

As per warwicktiger so formula would be

=ROUNDDOWN((A2-A1)*24,0)

gallygirl

Thanks, will give it a go later when on laptop .

SteveJW

Excel 2010 and above can do this without formulas
Not sure about earlier versions

e.g.

Cell A1 select custom format dd/mm/yyyy hh:mm type in (01/01/2015 06:30)
Cell B1 copy the custom format dd/mm/yyyy hh:mm type in(01/02/2015 06:45)
Cell C1 select custom format [h]:mm:ss
Cell C1 = B1 - A1 = 744:15:00
Format [h]:mm:ss disregards 24hrs

gallygirl

Can do the first part and calculate days, and multiply by 24 to give hours.

Can do the second part and calculate the difference in odd hours.

I can't manage to add the two together though as I can't get the same format in both answers.

Steve I have an earlier version and it won't work.

Any ideas, or just stop being so lazy and calculate manually?

SteveJW

gallygirl

What version of excel have you got and what operating system are you using

If I remember correct you can type in the formatting manually

Try starting a new workbook
Cell A1 right click, drop down menu, select format cells, select custom
If the format dd/mm/yyyy hh:mm is not available you can type it in manually

C:\Users\Administrator.WIN7-PC06\Pictures\Jackie\Excel.jpg

System

Hi

Apart from the formatting mess, I hope this is understandable

If you set the date formula as a custom, and enter it as given earlier, it seems to work quite nicely.

The number of hours is adjusted, adding 8 on for the 1st time difference and then taking 2 off for the second.

20 October 1940 21 November 2012 631920
20/10/1940 08:00 21/11/2012 16:00 631928
20/10/1940 18:00 21/11/2012 16:00 631918

14904 41234 =SUM(B1-1)*24
14904.3333333333 41234.6666666667 =SUM(B2-A2)*24
14904.75 41234.6666666667 =SUM(B3-A3)*24


Then changing the dates...

01 January 2015 01 January 2015 0
01/01/2015 08:00 01/01/2015 16:00 8
01/01/2015 18:00 01/01/2015 16:00 -2

SteveJW

Go to the following link and download the file, this is an example in Excel 97- 2003 format.
Guaranteed virus free
As previously stated no need to do any multiplication just take one date and time away from the other
If link doesn't work send me a PM

https://dl.dropboxusercontent.com/u/87238169/Date%20and%20Times.xls

Steve

cinderfella

Follow the following link to get the answer.

http://office.microsoft.com/en-gb/excel-help/calculate-the-difference-between-two-times-HP010070468.aspx