Formatting a columm for Dates

  • Thread starter Thread starter Jim Anderson
  • Start date Start date
J

Jim Anderson

Is there a way to set the format of a column so that dates can be entered as
numbers IE: 031204 for March 12, 2004 and have the formatting show it as
3/12/04? When I try setting formatting to dates using this format style,
the computer changes the numbers automatically to numbers I am not even
typing in. Thank you for any help. Jim
 
Thank you for the reply Norman. It seems a person has to be taught how to
use the VB module of Excel to accomplish this task. I read over the sites
and links you provided, attempted to copy and paste the script into the VB
program, but dont understand enough to make it function in the spreadsheet.
Must a person actually take a class in VB to enable the date change I am
interested in, or is there a "copy and paste" method and I am just missing a
small step? Thank you for you assistance.
Jim
 
Hi Jim!

You don't need VBA to achieve almost the same as you want but read on.

The problem is that Excel uses a date serial number for its dates with
the date serial number being the number of days since 31-Dec-1899.
When you format for dates you are merely "telling" Excel how to
represent the number that you are inputting.

When you input (eg) 030204, Excel interprets this as dateserial number
30204 which is 10-Sep-1982. Pre-formatting for date does not make a
difference because formatting only affects how a number is
represented; it does not affect the underlying entry.

Accordingly, using your rapid entry method, you will always get the
wrong date (although there are some numbers that give the same date in
the year 1900 and possibly others).

What you could do is enter using your system and then use a helper
column to parse the correct date.

Using ddmmyy as your date ordering system the parsing formula would
be:

=DATE(IF(--RIGHT(A1,2)<30,--RIGHT(A1,2)+2000,--RIGHT(A1,2)+1900),INT(A
1/10000),--RIGHT(INT(A1/100),2))

This formula is essentially what Chip is doing with his VBA code for
your entry form.

Having parsed your date in the helper column you can then use:

Copy
Edit > Paste Special > Values

Now you can delete your rapid entry dates.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hello Norman!
Thank you for all your efforts to help me out with this date entry problem.
I guess I will just go back to typing the dates in manually using the slash
or dash keys.
I have been able to set up various spreadsheets for my small business doing
basic adding, percentages etc, referencing others cells or pages, but I do
not understand what is meant by parsing formulas. Excel Help doesnt bring
up anything when I enter these items either. Clearly, the problem is my
lack of training on the deeper features of setting up spreadsheets. I wont
take up anymore of your time on this. I appreciate your efforts! Cheers!!
Jim
 
Hello Norman!
Well....Thank you...I fiddled around long enough with Macros and Creating
new Modules, and copying and pasting of the code from Chips web page, that
it finally "took"! It is working perfectly. Im not quite sure of the steps
I took to get there, but for now...I have the result I need. Thanks again.
Jim
 
Hi Jim!

Thanks. You made my day. After your last post, I thought we'd lost
you.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top