One cell calculation Excel - Time Interval [Solved/Closed]

yyzxyz - Jul 7, 2010 at 07:23 AM - Latest reply:  yyzxyz
- Jul 10, 2010 at 11:15 AM
Hello,

I need to calculate the elapsed time in hours if the start time and end time are contained in the same cell.

Example:

If A1 contains

5/10/2010 20:30 5/11/2010 20:20

B2 should have

24

Any help is greatly appreciated. Thank you.
See more 

4 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 7, 2010 at 11:26 AM
0
Thank you
So would your data always look like

<date 1><space><time 1><space><date2><space><time 2>

or could you have other combinations too ?
The data will always going to be in this format. Thanks again.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 7, 2010 at 07:36 PM
0
Thank you
Try this. You need to format the cell as number to decimal position as you desire. or you can send the formatted value from here like DateDiff = Format((CDate(sDt2) - CDate(sDt1)) * 24, "0")

Public Function DateDiff(ByVal sDateString As Range) As Variant 
Dim vPos As String 
Dim sDt1 As String 
Dim sDt2 As String 

    If Trim(sDateString) = vbNullString Then 
        DateDiff = vbNullString 
    Else 
        vPos = InStr(1, sDateString, " ") ' marks end of date part 1 
        vPos = InStr(vPos + 1, sDateString, " ")  ' marks end of time part 1 
         
        sDt1 = Left(sDateString, vPos - 1) ' date and time part 1 
        sDt2 = Mid(sDateString, vPos + 1) ' date and time part 2 
         
        DateDiff = (CDate(sDt2) - CDate(sDt1)) * 24 
    End If 
     
End Function 
0
Thank you
Thank you for the quick response and solution; it's extremely appreciated.