I need to calculate the number of hours and tenths of hours that have elapsed between two date-time groups. I can have the dates in A1 and A2 with the respective times (24-hour) in B1 and B2. One day = 24.0 hours. Every six minutes = 1/10th of an hour. Is there an easier way than separately calculating the first partial day + any whole days + last partial day?
Thanks. Any help is greatly appreciated. Rusty
Re: Date/Time Calculations in hours and tenths
like this ?
=INT(((A2+B2)-(A1+B1))*24) & " Hours " & ROUND(MOD(((A2+B2)-(A1+B1))*24,1),1) & " tenths"
Re: Date/Time Calculations in hours and tenths
Hi RustyS
Welcome to ozgrid
Following on from Kieran's great example, I would keep the hours and the fraction for minutes in sepertate cells. Then you can use them in further calculations.
Based on the later date being in A1, it's time residing in B1 and the earlier date being in A2 with its time in B2 you could use;
=INT(SUM(A1:B1)-SUM(A2:B2))
and Custom Format the cell as [h] to show hours past 24
In a cell next to this for the fraction, place
=MOD(SUM(A1:B1)-SUM(A2:B2),1)*24
and format it as a Fraction, up 2 digits.
See: Excel Date and Times for details on how Excel sees them.
Re: Date/Time Calculations in hours and tenths
Kieran and Dave, :thanx:
Thank you for the help. Its actually working. What I'm doing is working with newborns and some testing that is done before they are released from the hospital. I have the birth date and time and also the date and time the test was started and when it ended as well as the results of the test.
Using your formulae, I will be able to so some research into the significance of the time interval between birth and test as well as time in the test, itself. The results of the test (pass/fail) produced a series of sub-groups that will be loaded into SPSS for further analysis. You, both, broke the code for me and I really appreciate it. Rusty
Don’t have an account yet? Register yourself now and be a part of our community!
