Transform Date & Time

[bdenn]

Hello,
I have a large data set which I filter by hour. Within each hour I could have lots of random times (02:00:12, 02:00:21, 02:00:35, etc.) is it possible to transform all of these date/time entries to display each entry as 02:00:00.

I would like to not add another column if possible.

Thanks,
Bdenn

 

[Kerryx]

Not my code but tested on Column A and works

Option Explicit 'First line at your module ensures you declare any variables

Public Sub FloorFormat()
    Dim lastRow As Long
    
    With Worksheets("Sheet1") 'Your sheet name here
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row 'find last used row
        
        Dim i As Long
        For i = 1 To lastRow 'do the following for all used rows in column A
            .Cells(i, "A").Value = Application.WorksheetFunction.Floor(.Cells(i, "A").Value, 1 / 24)
        Next i
    End With
End Sub

 

[bdenn]

Hello,
Is it possible without using VBA?

Thanks,
Bdenn

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Time", type time}}),
    tbl = Table.TransformColumns(ChangeType, {{"Time", each #time(Time.Hour(_) ,0,0)}})
in
    tbl

Book4
	A	B	C	D
1	Time		Time
2	6:01:58 PM		6:00:00 PM
3	1:16:30 PM		1:00:00 PM
4	10:19:24 PM		10:00:00 PM
5	7:15:31 PM		7:00:00 PM
6	3:51:16 PM		3:00:00 PM
7	2:15:09 AM		2:00:00 AM
8	12:49:32 PM		12:00:00 PM
9	10:08:32 AM		10:00:00 AM
10	12:08:17 PM		12:00:00 PM
11	10:35:54 PM		10:00:00 PM
12	9:09:07 AM		9:00:00 AM
13	7:15:37 PM		7:00:00 PM
14	9:12:48 AM		9:00:00 AM
15	5:36:11 AM		5:00:00 AM
16	5:39:01 AM		5:00:00 AM
17	8:53:30 AM		8:00:00 AM
18	4:53:42 PM		4:00:00 PM
19	5:12:22 PM		5:00:00 PM
20	3:47:11 AM		3:00:00 AM
21	10:45:59 AM		10:00:00 AM
22	10:47:49 AM		10:00:00 AM
23	4:46:01 AM		4:00:00 AM
24	9:00:48 PM		9:00:00 PM
25
Sheet1

 

[bdenn]

Thanks! Just what I was looking for.

-Bdenn