Turning off the @ function

[peterfitz]

I want to enter a date and time as: "10/14/2009@2:28 pm"
How can I do this without having Excel turn it into an email address? I tried pre formating as text, custom formatting, and the only solution I have come up with is to precede the date and time with an inverted comma. I couldn't find anything on the Excel help.
Is there a global 'off' for this function?? Any suggestions would be welcome. Thank you.

 

[BoozeBoy]

Have you tried custom formatting as:
mm/dd/yy"@"h:mm AM/PM;@

 

[peterfitz]

Not like you have it. I'll give it a try. Thank you for the suggestion.

Peter

 

[peterfitz]

Gave it a go but it didn't work. I also tried with a hh in case that was a typo. Found another work around in that if you type it in one cell then use 'Paste Special> values' - to another cell it pastes as regular text. Perhaps that may spark another thought on how to get around the problem.

As they will be entering 20+ of these per day, I'd much rather find a format cells solution if possible.

Peter

 

[snowblizz]

BoozeBoy's suggestion worked for me.

Entering the date as: dd-mm-yy h:mm
yielded dd/mm/yy @ h:mm as a result.
Ie you need to make sure Excel understands it as a date and time when entered.

 

[peterfitz]

Thanks for the follow up!

Here's a cut and paste from my custom format window showing my entry for formatting the column:

mm/dd/yy"@"h:mm AM/PM;@

Here's what I get when I enter the date and time in a cell:;-
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17><TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>10/14/09@12:36 pm</TD></TR></TBODY></TABLE>(showing as a blue underlined link)</TD></TR></TBODY></TABLE>It seems I am making some basic error here...

 

[Richard Schollar]

Assuming you're using xl2003 or below go Tools>Autocorrect Options>Autoformat as you type tab>uncheck "Internet and network paths with hyperlinks"

 

[snowblizz]

Strangely enough I do not get that problem, and I have the autocorrect on.
Hmm ok tried one thing.
Type the numbers but do NOT include the "@". Just the numbers.
The format will place the "@" for you.

 

[peterfitz]

Both solutions work! - i.e. ommiting the "@" during data entry, and by turning off the option through tools.

So that I learn from this, Explicimu two points in your solution Snowblizz, why only one h for hours not hh:mm, and whast does the ;@ at the end of the formula do?

I appreciate all the input. It has resolved my issue.

peter