How to split and expand date and hours?

[Fractalis]

Hi everyone,

How to split and expand the date and time with a formula or VBA?

I have the "start date and time" that begins in one date and sometimes ends in next day. For this cases I'd like to split in two
rows from begin date until same date up to 23:59 and continue in next row the next date until end hour. For the cases where start
date and end date is the same only split in one row.

Below is the input 2 columns with blue headers and output I'd like to get in green headers.

Start Date & Time	End Date & Time		Start Date	Start Time	End Date	End Time
10/13/2022 23:44	10/14/2022 2:20		2022-10-13	23:44	2022-10-13	0:59
10/24/2022 23:17	10/24/2022 23:57		2022-10-14	0:00	2022-10-14	2:20
			2022-10-24	23:17	2022-10-24	23:51

Thanks in advance.

 

[Dave Patton]

Time.xlsm
	A	B	C	D	E	F	G	H
1								info
2	Start Date & Time	End Date & Time		Start Date	Start Time	End Date	End Time
3	13-Oct-22 23:44	14-Oct-22 02:20	02:36	13-Oct-22	23:44:20	00:00:00	23:59:59	00:15:39
4	24-Oct-22 23:17	24-Oct-22 23:57	00:40	14-Oct-22	00:00	14-Oct-22	02:20:25	02:20:25
5				24-Oct-22	23:17:19	24-Oct-22	23:57:43	00:40:24
6
7
11a

Cell Formulas
Range	Formula
D3	D3	=INT(A3)
E3	E3	=MOD(A3,1)
H3:H5	H3	=G3-E3
C3:C4	C3	=B3-A3
G4:G5	G4	=MOD(B3,1)
E5	E5	=MOD(A4,1)

 

[Fractalis]

Thanks Dave for your answer. I'd like to get a way to dynamically do the expand to 2 rows when needed.
In this sample only show 2 input rows, but actual data has several input dates and times.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[kvsrinivasamurthy]

post deleted

 

[jaeiow]

365 methods: Instead of absolute dell references, use DROP(FILTER(A:A,A:A<>""),1), or bring it in a =lambda helper like this

=DROP(REDUCE(0,$A3:INDEX($A3:$A5001,COUNTA($A3:$A5001)),LAMBDA(a,b,VSTACK(a,INT(b)))),1)

 

[kvsrinivasamurthy]

A	B	C	D	E	F	G	H
	Start Date & Time	End Date & Time		Start Date	Start Time	End Date	End Time
	13-10-2022 23:44​	15-10-2022 02:20​		13-10-2022	23:44:00​	13-10-2022	23:59:59​
	24-10-2022 23:17​	24-10-2022 23:57​		14-10-2022	00:00:00​	14-10-2022	23:59:59​
				15-10-2022	00:00:00​	15-10-2022	02:20:00​
				24-10-2022	23:17:00​	24-10-2022	23:57:00​

Code for macro

Sub Rearrange_Dates()
Dim A, K
Dim T As Long, Ta As Long, dys As Long, X As Long
A = Range("B3").CurrentRegion

With CreateObject("SCripting.Dictionary")
For T = 2 To UBound(A, 1)
dys = Int(A(T, 2)) - Int(A(T, 1))
If dys = 0 Then
X = X + 1: .Add X, Array(Int(A(T, 1)), A(T, 1) - Int(A(T, 1)), Int(A(T, 2)), A(T, 2) - Int(A(T, 2)))
Else
    For Ta = 0 To dys
    X = X + 1
    ReDim ary(0 To 3)
    If Ta = 0 Then
    ary(0) = Int(A(T, 1)): ary(1) = A(T, 1) - Int(A(T, 1)): ary(2) = Int(A(T, 1)): ary(3) = TimeValue("23:59:59")
    ElseIf Ta < dys Then
    ary(0) = Int(A(T, 1)) + Ta: ary(1) = TimeValue("0:00:00"): ary(2) = Int(A(T, 1)) + Ta: ary(3) = TimeValue("23:59:59")
    Else
    ary(0) = Int(A(T, 1)) + Ta: ary(1) = TimeValue("0:00:00"): ary(2) = Int(A(T, 1)) + Ta: ary(3) = A(T, 2) - Int(A(T, 2))
    End If
        
    .Add X, ary
    Next Ta
End If
Next T
K = .items
End With

With Range("E3")
.CurrentRegion.Offset(1, 0).ClearComments
.Offset(1, 0).Resize(X, 4) = WorksheetFunction.Index(K, 0, 0)
.CurrentRegion.Offset(1, 0).Columns(2).NumberFormat = "hh:mm:ss"
.CurrentRegion.Offset(1, 0).Columns(4).NumberFormat = "hh:mm:ss"
End With
End Sub

 

[Dave Patton]

a relatively simple alternative

Time.xlsm
	A	B		D	E	F
10	Start Date & Time	End Date & Time		Time Start Day	Time Next day	Total
11	13-Oct-22 23:44	14-Oct-22 02:20		00:15	02:20	02:36
12	24-Oct-22 23:17	24-Oct-22 23:57		00:40	00:00	00:40
13
11a

Cell Formulas
Range	Formula
D11:D12	D11	=IF(INT(B11)>INT(A11),INT(B11)-A11,B11-A11)
E11:E12	E11	=IF(INT(B11)>INT(A11),MOD(B11,1),0)
F11:F12	F11	=D11+E11

 

[Fractalis]

A	B	C	D	E	F	G	H
	Start Date & Time	End Date & Time		Start Date	Start Time	End Date	End Time
	13-10-2022 23:44​	15-10-2022 02:20​		13-10-2022	23:44:00​	13-10-2022	23:59:59​
	24-10-2022 23:17​	24-10-2022 23:57​		14-10-2022	00:00:00​	14-10-2022	23:59:59​
				15-10-2022	00:00:00​	15-10-2022	02:20:00​
				24-10-2022	23:17:00​	24-10-2022	23:57:00​

Code for macro

Sub Rearrange_Dates()
Dim A, K
Dim T As Long, Ta As Long, dys As Long, X As Long
A = Range("B3").CurrentRegion

With CreateObject("SCripting.Dictionary")
For T = 2 To UBound(A, 1)
dys = Int(A(T, 2)) - Int(A(T, 1))
If dys = 0 Then
X = X + 1: .Add X, Array(Int(A(T, 1)), A(T, 1) - Int(A(T, 1)), Int(A(T, 2)), A(T, 2) - Int(A(T, 2)))
Else
    For Ta = 0 To dys
    X = X + 1
    ReDim ary(0 To 3)
    If Ta = 0 Then
    ary(0) = Int(A(T, 1)): ary(1) = A(T, 1) - Int(A(T, 1)): ary(2) = Int(A(T, 1)): ary(3) = TimeValue("23:59:59")
    ElseIf Ta < dys Then
    ary(0) = Int(A(T, 1)) + Ta: ary(1) = TimeValue("0:00:00"): ary(2) = Int(A(T, 1)) + Ta: ary(3) = TimeValue("23:59:59")
    Else
    ary(0) = Int(A(T, 1)) + Ta: ary(1) = TimeValue("0:00:00"): ary(2) = Int(A(T, 1)) + Ta: ary(3) = A(T, 2) - Int(A(T, 2))
    End If
       
    .Add X, ary
    Next Ta
End If
Next T
K = .items
End With

With Range("E3")
.CurrentRegion.Offset(1, 0).ClearComments
.Offset(1, 0).Resize(X, 4) = WorksheetFunction.Index(K, 0, 0)
.CurrentRegion.Offset(1, 0).Columns(2).NumberFormat = "hh:mm:ss"
.CurrentRegion.Offset(1, 0).Columns(4).NumberFormat = "hh:mm:ss"
End With
End Sub

Thanks for your answer. not sure why but I getting run time '13' type mismatch in this line "dys = Int(A(T, 2)) - Int(A(T, 1))".

 

[Fractalis]

a relatively simple alternative

Thanks for your answer. I'm getting error "#VALUE" in this part in red

IF(INT("10/14/2022 2:20")>INT(A11),INT(B11)-A11,B11-A11)