ExcelBanter - View Single Post

(PeteCresswell)

Per :
How can I calculate the time elapsed between two given times?
For example if I input initial time and date as 20:00 on 03 Jan 06 and
final time and date as 05:00 on 05 Jan 06,

How about this: create a macro to do the heavy lifting and then invoke the
macro in the cell that you want the calc result in.

The macro would look something like this (subject to your modifying the
rounding/truncation):
------------------------------------------------------------------------
Function HoursDiff(ByVal theBeginDateTime As Variant, ByVal theEndDateTime As
Variant) As Long

'PURPOSE: To calculate the difference in hours between two Date/Time values.
'ACCEPTS: - Beginning Date/Time value
' - Ending Date/TimeValue
'RETURNS: Number of hours between the two values
'
Dim myDiff As Long

myDiff = DateDiff("n", theBeginDateTime, theEndDateTime)
myDiff = myDiff / 60

HoursDiff = myDiff

End Function
---------------------------------------------------------------------------

Then, in the cell you want the result in, you put something this into cell A1:

=HoursDiff(E1, F1)

Then type "01/03/2005 20:00:00" into cell E1 and type "01/05/2005 17:00" into
cell F1 and then "45" should magically appear in cell A1.

I could email you the sample spreadsheet - but with one caveat: I don't know
beans about Excel and once I created the function I couldn't find it again...
it's there because I'm getting the results I expect...it's just hiding somewhere
and anybody who knows Excel could probably find it.
--
PeteCresswell