How can I format an hhmmAM/PM (no space) to time hh:mm AM/PM in excel? -

How can I format an hhmmAM/PM (no space) to time hh:mm AM/PM in excel? -

i'm working on payroll project , create info entry more efficient want time entered in format hhmmam/pm, no spaces or colons , preferable typing a/p instead of am/pm , have convert standard time format colons , spaces. thanks!

here little macro coded column a. can modified handle set of cells. macro monitors entries in column a , and changes them values 0745a or 0745p normal times. come in next event macro in worksheet code area:

private sub worksheet_change(byval target range) if intersect(target, range("a:a")) nil exit sub application.enableevents = false s = target.text if right(s, 1) = "a" or right(s, 1) = "a" s2 = " am" else s2 = " pm" end if target.value = left(s, 2) & ":" & mid(s, 3, 2) & s2 application.enableevents = true end sub

because worksheet code, easy install , automatic use:

right-click tab name near bottom of excel window select view code - brings vbe window paste stuff in , close vbe window

if have concerns, first seek on trial worksheet.

if save workbook, macro saved it. if using version of excel later 2003, must save file .xlsm rather .xlsx

to remove macro:

bring vbe windows above clear code out close vbe window

to larn more 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 larn more event macros (worksheet code), see:

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

macros must enabled work!

excel format