One cell calculation Excel - Time IntervalSolved/Closed

Question

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.

rizvisa1 · Answer

So would your data always look like or could you have other combinations too ?

rizvisa1 · Answer

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

yyzxyz · Answer

Thank you for the quick response and solution; it's extremely appreciated.

One cell calculation Excel - Time Interval

3 responses

Similar discussions

Subscribe To Our Newsletter!