How to enter non-military time without colon and with am/pm

[Sha123123]

I would like to enter time in excel without having to type a colon, and to be able to designate am/pm. I don't want to use military time. I have tried doing a custom format of 0\:00 and got the colon to appear automatically but when I tried to add am/pm, the formula failed. Thank you! Sha

[Tony Valko]

Maybe this...

http://www.cpearson.com/Excel/DateTimeEntry.htm

[Sha123123]

thanks but I didn't understand the link info at all. Sha

[Toonies]

Hi Sha,

Tony has giving a VBA solution. You have to have Macros enabled to use

Simply format your cells to

Please Login or Register  to view this content.

Enter Time as 2300 it will change to 11:00pm etc

Here is Tonys Example, plus another solution

Please Login or Register  to view this content.

To alter the range simply amend this part of the code

Please Login or Register  to view this content.

I think this is a shortened version of the above code - I do believe its Tonys Valko code

Please Login or Register  to view this content.

[Sha123123]

tx toonies. I am very basic in my knowledge and I don't understand how to apply tony's and your info. I opened the example but am still lost. sha

[Sha123123]

oh plus I am trying to enter standard time, not military time. tx again! sha

[Toonies]

can you post an example of you spreadsheet

click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Sha123123]

toonies, I hope I uploaded the attachment correctly. tk you! sha

[mehmetcik]

Your Columns C and D are already formatted corectly

Enter 10:27 a and press enter

[protonLeah]

Use helper columns (D & F) which shall be hidden:
In D6:

Please Login or Register  to view this content.

and F6 replacing C6 in formula with E6.

[Toonies]

Hi Simple Combine protonLeah's formulas and you should not need helper columns

Please Login or Register  to view this content.

[Toonies]

Hi Simple Combine protonLeah's formulas and you should not need helper columns

Please Login or Register  to view this content.

[Phuocam]

Another way

=--(TEXT(LEFT(C6,LEN(C6)-2),"0\:00 ")&RIGHT(C6)&"M")

[Sha123123]

hi, thank you to everyone who responded. sorry for the late reply. my computer doesn't have software so I have to go to the library to use their computer and my car broke down.

I read all the replies. I tried pasting some of the formulas into the total time cell but that did not seem to work. as for the helper columns, I am not sure what they are would rather keep the spreadsheet as simple as possible. thanks so much!

sha

[Sha123123]

okay, I tried again and protonleah, your helper columns worked! yea! then toonies said I could just combine her formulas to eliminate the need for the helper columns. but where do I paste the formula that toonies suggested? I tried pasting into the cell where the time entry goes into but the prompt said it is a circular something. tx for your patience! sha

[protonLeah]

The combined formula computes the total time so it goes in E6

[Sha123123]

thank you protonLeah, I tried it and it worked well until I tried a test entry of start time of 9:30 am and end time of 12:45pm. the end time came up as 12:45am instead..i had no trouble when I entered time that did not involve 12 am or pm..sha

[Sha123123]

thank you mehmetcik, i am still having trouble. sha

[Sha123123]

thank you Phuocam, sorry but where do i enter your formula? sha