# How to split and expand date and hours?



## Fractalis (Dec 20, 2022)

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 & TimeEnd Date & TimeStart DateStart TimeEnd DateEnd Time10/13/2022 23:4410/14/2022 2:202022-10-1323:442022-10-130:5910/24/2022 23:1710/24/2022 23:572022-10-140:002022-10-142:202022-10-2423:172022-10-2423:51 

Thanks in advance.


----------



## Dave Patton (Dec 20, 2022)

Time.xlsmABCDEFGH1info2Start Date & TimeEnd Date & TimeStart DateStart TimeEnd DateEnd Time313-Oct-22 23:4414-Oct-22 02:2002:3613-Oct-22 23:44:2000:00:0023:59:5900:15:39424-Oct-22 23:1724-Oct-22 23:5700:4014-Oct-22 00:0014-Oct-22 02:20:2502:20:25524-Oct-22 23:17:1924-Oct-22 23:57:4300:40:246711aCell FormulasRangeFormulaD3D3=INT(A3)E3E3=MOD(A3,1)H3:H5H3=G3-E3C3:C4C3=B3-A3G4:G5G4=MOD(B3,1)E5E5=MOD(A4,1)


----------



## Fractalis (Dec 20, 2022)

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 (Dec 21, 2022)

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 (Dec 21, 2022)

post deleted


----------



## jaeiow (Dec 21, 2022)

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 (Dec 21, 2022)

ABCDEFGHStart Date & TimeEnd Date & TimeStart DateStart TimeEnd DateEnd Time13-10-2022 23:44​15-10-2022 02:20​13-10-202223:44:00​13-10-202223:59:59​24-10-2022 23:17​24-10-2022 23:57​14-10-202200:00:00​14-10-202223:59:59​15-10-202200:00:00​15-10-202202:20:00​24-10-202223:17:00​24-10-202223: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 (Dec 21, 2022)

a relatively simple alternative
Time.xlsmABDEF10Start Date & TimeEnd Date & TimeTime Start DayTime Next dayTotal1113-Oct-22 23:4414-Oct-22 02:2000:1502:2002:361224-Oct-22 23:1724-Oct-22 23:5700:4000:0000:401311aCell FormulasRangeFormulaD11:D12D11=IF(INT(B11)>INT(A11),INT(B11)-A11,B11-A11)E11:E12E11=IF(INT(B11)>INT(A11),MOD(B11,1),0)F11:F12F11=D11+E11


----------



## Fractalis (Dec 21, 2022)

kvsrinivasamurthy said:


> ABCDEFGHStart Date & TimeEnd Date & TimeStart DateStart TimeEnd DateEnd Time13-10-2022 23:44​15-10-2022 02:20​13-10-202223:44:00​13-10-202223:59:59​24-10-2022 23:17​24-10-2022 23:57​14-10-202200:00:00​14-10-202223:59:59​15-10-202200:00:00​15-10-202202:20:00​24-10-202223:17:00​24-10-202223:57:00​
> 
> Code for macro
> 
> ...


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 (Dec 21, 2022)

Dave Patton said:


> 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)


----------



## Fractalis (Dec 20, 2022)

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 & TimeEnd Date & TimeStart DateStart TimeEnd DateEnd Time10/13/2022 23:4410/14/2022 2:202022-10-1323:442022-10-130:5910/24/2022 23:1710/24/2022 23:572022-10-140:002022-10-142:202022-10-2423:172022-10-2423:51 

Thanks in advance.


----------



## Dave Patton (Dec 21, 2022)

Your post #10 is using text,.   Access the date cell.
N.B. You can copy my post to a clean sheet and review the formulas. Click on the icon below the f(x) in the header , move to sheet and paste.


----------



## Dave Patton (Dec 21, 2022)

I reposted the information showing the Date/Time with General Format.
You can format the Date/Time to your preference but do not convert to Text.
I included the seconds so that you see that the numbers add correctly.

Time.xlsmABCDE1Start Date & TimeEnd Date & TimeTotalTime Start DayTime End Day244847.989144848.097502:36:0500:15:4002:20:25344858.970444858.998400:40:2400:40:2400:00:00411aCell FormulasRangeFormulaC2:C3C2=B2-A2D2:D3D2=IF(INT(B2)>INT(A2),INT(B2)-A2,B2-A2)E2:E3E2=IF(INT(B2)>INT(A2),MOD(B2,1),0)

Time.xlsmABCDE1Start Date & TimeEnd Date & TimeTotalTime Start DayTime End Day244847.989144848.097502:36:0500:15:4002:20:25344858.970444858.998400:40:2400:40:2400:00:00411aCell FormulasRangeFormulaC2:C3C2=B2-A2D2:D3D2=LET(x,INT(B2),IF(x>INT(A2),x-A2,B2-A2))E2:E3E2=IF(INT(B2)>INT(A2),MOD(B2,1),0)


----------



## kvsrinivasamurthy (Dec 21, 2022)

My default settings for date is dd/mm/yy. Yours is mm/dd/yy. Pl check.
The dates may be in text fomat. Change it to date format.
If problem not solved, pl upload file.


----------



## Fractalis (Dec 21, 2022)

Dave Patton said:


> I reposted the information showing the Date/Time with General Format.
> You can format the Date/Time to your preference but do not convert to Text.
> I included the seconds so that you see that the numbers add correctly.



 I copied as you suggested and I see the same output as you, but is different than the output I'm looking for


----------



## Fluff (Dec 22, 2022)

@Fractalis please see post#4.


----------



## Fractalis (Dec 22, 2022)

Fluff said:


> @Fractalis please see post#4.


Hi. I'm using Excel 2019


----------

