如何将hhmmam / PM（无空格）格式化为hh：mm AM / PM in excel？ [英] How can I format an hhmmAM/PM (no space) to time hh:mm AM/PM in excel?

问题描述

我正在开发一个工资项目，并使数据输入更有效率我希望以hhmmAM / PM格式输入时间，没有空格或冒号，最好只输入一个/ p而不是AM / PM以冒号和空格转换为标准时间格式。谢谢！

解决方案

这是一个代码为 A 的小型宏。它可以修改为处理任何一组单元格。该宏监视列 A 中的条目，并将它们从 0745a 或 0745p 等值更改为正常时间。在工作表代码区域中输入以下事件宏：

  Private Sub Worksheet_Change（ByVal Target As Range）
如果相交（Target，Range（A：A））Is Nothing Then Exit Sub 
 Application.EnableEvents = False 
s = Target.Text 
如果Right（s，1）=a或右（s，1）=A然后
 s2 =AM
 Else 
 s2 =PM
 End If 
 Target.Value = Left s，2）& ：&中（3，2）& s2 
 Application.EnableEvents = True 
 End Sub 
  

因为它是工作表代码，这很容易安装和自动使用：

1. 右键单击Excel窗口底部附近的选项卡名称


2. 选择查看代码 - 这将打开一个VBE窗口


3. 将东西粘贴并关闭VBE窗口

如果您有任何疑虑，请先试用试用版。

如果保存工作簿，则宏将与它一起保存
如果您在2003年之后使用Excel版本，则必须将
保存为.xlsm而不是.xlsx

要删除宏：

1. 打开上面的VBE窗口


2. 清除代码


3. 关闭VBE窗口

要了解有关宏的更多信息，请参阅：

http://www.mvps.org/dmcritchie/ excel / getstarted.htm

和

http://msdn.microsoft.com/en-us/library/ee814735（v = office .14）.aspx

要了解有关事件宏（工作表代码）的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/event.htm

必须启用宏才能使其工作！

I'm working on a payroll project and to make data entry more efficient I want the time entered in a format with hhmmAM/PM, no spaces or colons and preferable just typing a/p instead of AM/PM and have that convert to standard time format with colons and spaces. Thanks!

解决方案

Here is a small macro that is coded for column A. It can be modified to handle any set of cells. The macro monitors entries in column A and and changes them from values like 0745a or 0745p into normal times. Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        s = Target.Text
        If Right(s, 1) = "a" Or Right(s, 1) = "A" Then
            s2 = " AM"
        Else
            s2 = " PM"
        End If
        Target.Value = Left(s, 2) & ":" & Mid(s, 3, 2) & s2
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

这篇关于如何将hhmmam / PM（无空格）格式化为hh：mm AM / PM in excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！