Days and dates in Excel

[Pat Mooney]

I'm tired of entering slashes to tell Excel that the
numbers I'm entering are dates and colons to tell Excel
that it's dealing with times. I asked a similar question
here several weeks back and one of the responses gave me a
link which took me to a site where a software guru had
posted a formula to do this. Unfortunately, no one told
me where to put this in Excel when I cut and paste it from
the website.

Then I got to thinking that Excel has under soecial
formats formats to allow fast entry of SSN's and ZIP
codes. When you enter nine numbers in a cell formated for
SSN's it enters the dashes in the appropriate places.
Where's this format? If I could find it, I could copy it
and make the changes I want to the copy, save that as a
custom format, and format my cells to that. Please help
me. Thanks!

 

[Myrna Larson]

You can use a custom number format "00\/00\/\0000". But don't try to do date arithmetic with
these values. You won't get the correct answers.

if you type 8312003 for Aug 31 2003, you'll have the number 8,312,003. The date serial number
for that date is 37864.

 

[J.E. McGimpsey]

You have a fundamental, though common, misconception. Formats
control how values are *displayed*, only. The parser has separate
rules about interpreting entries, which are independent of the
cell's format.

You can use something similar to SSN formats (which are found in
Format/Cells/Special...) to make a number look like a time, e.g,:

Format/Cells/Number/Custom 00:00

which will cause an entry of 1234 to display as 12:34. But it will
not be recognized as a time by XL. For instance, if the above were
entered in A1, then

A2: =A1+TIME(1,26,0)

will display 12:34 rather than 14:00, since times in XL are stored
as fractional days (e.g., 1:26 = 0.059722222, so the stored result
will be 1234.0597222222). Unless you only want to display the times,
the format solution will be unacceptable.

The situation is different with SSN's and ZIPs - the SSN is stored
as an integer, for example:

012-34-5678

is actually store as the number 12345678. This is OK for most
purposes, but leads to confusion if you try to export it, since the
leading zero is not retained. Better to enter SSN's as text strings,
since you'll never have to do math on them.

As far as no one telling you where to put the code, did you ask? The
responder(s) probably couldn't tell from your post whether you had
worked with macros or event macros - and assumed you'd ask for more
help if you hadn't.

In any case, event macros are the only way within XL/VBA to get to
where you want to go, at least without using helper cells. I suspect
your previous answer involved Chip Pearson's event macros:

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

In addition to an article on the same site that explains where code
should go:

http://www.cpearson.com/excel/codemods.htm

you might want to look at David McRitchie's getting started with
macros page:

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