• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

Excel vba question

Hi, my excel code returns the time in military time - my OS is not set to military time, how can I take the time function out of returning military time?

I am using Excel 2010

Thanks much,

Dim x As Date

x = Format(Date, "mm-dd-yyyy")

Selection = x & "---" & Time

  • 2
1 Solution
Dave BrettVice President - Business EvaluationCommented:
You can force AM:PM as below


Dim x As Date

Selection = Format(Date, "mm-dd-yyyy") & "--" & Format(Time, "hh:mm:ss AMPM")

Open in new window

rinkydinkAuthor Commented:
Yes, I tried using Format(Time, "hh:mm:ss ampm") but it still returns the time in military time format.

I have the same OS Regional settings on my pc at home and Selection=Time returns the time in hh:mm:ss AMPM but I run Office 2007 at home and 2010 at work but I can find no setting in 2010 that appears to be forcing military time.

Hi Rinkydink

If you declare x as a date that is a value and is not concerned with the visible format.

It depends more on where you are displaying the date.

if its a worksheet cell i would tend to do as per code below.

Hope this helps

Option Explicit

Sub Macro1()
Dim r As Range, x As Date

x = Now
Set r = Sheet1.Range("A1")
r.Value = x
r.NumberFormat = "h:mm AM/PM"
End Sub

Sub Macro2()
Dim r As Range, x As Date

'better way to input times and dates. gives more control
x = DateSerial(Year(Date), Month(Date), Day(Date)) + TimeSerial(Hour(Now), Minute(Now), 0)

Set r = Sheet1.Range("A2")
r.Value = x
r.NumberFormat = "d mmm yyyy h:mm AM/PM"
End Sub

'if its in a vba form and you want to output a readable string
'use the value as follows

Dim strDate as String
Dim x as Date

x= Now
strDate=Format(x,"d mmm yyyy h:mm AM/PM")

Open in new window

Oh i should have mentioned,
Macro1 and Macro2 are the same except for using Dateserial and Timeserial
I just wanted to point out the value of those functions because it makes it easy to leave out the seconds if you don't actually want them. It also makes it easy to get dates such as last date of previous month, Last quarter, and things like that.

Best Regards
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now