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

Report
-
 yyzxyz -
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.

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
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 
Thank you for the quick response and solution; it's extremely appreciated.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!