Round Time Down to Zero Seconds

Microsoft Excel 2003 (full)
June 18, 2010 at 20:59:52
Specs: Windows XP
Using VBA, I want to place the current time into a cell and then round-down to zero seconds.

ActiveCell.Value = Time
Floor(ActiveCell,1/1440) ? ? ? ?
ActiveCell.NumberFormat = "h:mm"
ActiveCell.Offset(1, 0).Select

See More: Round Time Down to Zero Seconds

Report •

June 18, 2010 at 23:26:00
This should be posted here:

Report •

June 20, 2010 at 04:30:16
Thanks for the link. I have searched there. Maybe I'm confused but I cannot get '=A1-SECOND(A1)/86400' to work in VBA. How do I code this using VBA and include the code in a macro? Maybe I should ask how do I run a function in a macro?

Report •

June 20, 2010 at 05:38:15
Re:Maybe I should ask how do I run a function in a macro?
Why would you want to run the Excel worksheet SECOND() function in VBA.
VBA already has a Second function.
Type "Second(" in the VBA window and it will prompt you for time as an argument.

Here is a short macro that will enter the time with seconds in cell A1 and the time rounded down to zero seconds in cell B1.
The code is in the Worksheet("Sheet1") object
Format cells A1 and B1 with "hh:mm:ss"
Run the macro and see the difference.

Sub NoSeconds()
Dim n As Date
n = Now()
Worksheets("Sheet1").Range("A1").Value = n
Worksheets("Sheet1").Range("B1").Value = n - Second(n) / 86400
End Sub


Report •

Related Solutions

June 20, 2010 at 18:32:49
Exactly what I need. Now, how do I get cell B1 to display the time in hh:mm format (military time)? When I select cell B1, I see the date followed by the time. All I want to see is h:mm:00 AM/PM.

Report •

June 20, 2010 at 19:17:42
Figured it out. I'm a VBA newbie!
I changed the n = Now() to n = Time().

Report •

Ask Question