CODEWITHSUNDEEP
vbaexceltime
Syukrindang
Syukrindang

Reputation: 41

How to display hh:mm:ss in Excel?

I'm new to Excel VBA. I want to set up a loop that will display the time for each minute on the minute in a new cell.

Is there a way to display only the time (hh:mm:ss) in a cell without seeing the date beside it? Also, I'd like the time zone shown. (e.g. Singapore Time, "SGT").

Here is an example of the output I'd like to see:

23-Oct-13 10:00:00 AM SGT
23-Oct-13 10:01:00 AM SGT
23-Oct-13 10:02:00 AM SGT
23-Oct-13 10:03:00 AM SGT
23-Oct-13 10:04:00 AM SGT
23-Oct-13 10:05:00 AM SGT
23-Oct-13 10:06:00 AM SGT
23-Oct-13 10:07:00 AM SGT
23-Oct-13 10:08:00 AM SGT
23-Oct-13 10:09:00 AM SGT
23-Oct-13 10:10:00 AM SGT
23-Oct-13 10:11:00 AM SGT
23-Oct-13 10:12:00 AM SGT
23-Oct-13 10:13:00 AM SGT
23-Oct-13 10:14:00 AM SGT
23-Oct-13 10:15:00 AM SGT
23-Oct-13 10:16:00 AM SGT
23-Oct-13 10:17:00 AM SGT
23-Oct-13 10:18:00 AM SGT
23-Oct-13 10:19:00 AM SGT
23-Oct-13 10:20:00 AM SGT
...

Upvotes: 1

Views: 2593

Answers (3)

0m3r
0m3r

Reputation: 12499

This Should do it, have Fun. :-) 

Option Explicit
Sub test()
    Dim i&, sTime As Date, TimeStep As Date, Cell As Range
    '// Format range cell to 24Hrs
    With Range("A1:A1440") '// 24H*60M
        .NumberFormat = "HH:MM:SS;@"
    End With

    Set Cell = [A1] '// first cell to write
        sTime = "00:00:00"  '// start time
    '// Loop 24hrs
    TimeStep = "00:01:00" '// time increment
    For i = 0 To 1439 ' // 1439 means 1439 cells
        Cell.Offset(i, 0).Value = sTime
        sTime = sTime + TimeStep
    Next i
End Sub

Start

enter image description here

End

enter image description here

Upvotes: 4

Fabrizio
Fabrizio

Reputation: 662

To retrieve time format you can use:

=Mid(A1,11,8)*1 into function bar (with additional column)

or into a sub

timeOnClock = Mid(MyString, 10, 9)*1

then for your first row (23-Oct-13 10:00:00 AM SGT) you retrieve 0,416666667 and you can choice your prefer format

Upvotes: 0

Avishay Cohen
Avishay Cohen

Reputation: 2208

ok, now that you explained a bit in more depth,

ActiveSheet.Cells(1, 1).Value = "00:00:00"
ActiveSheet.Cells(2, 1).Value = "00:01:00"
Range("A1:A2").Select
Selection.AutoFill Destination:=Range("A1:A1440"), Type:=xlFillDefault

should do the trick

Upvotes: 3

Related Questions