Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Date Formatting

  1. #11
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    > With the two digit year representation,
    > caution...

    I am not a proponent of m/d/yy format. I remember Y2K.

    > It is interesting that conversion of text
    > strings using your formula works with the
    > '-' character but not with the '/'
    > character, although generally Excel will
    > accept date with either of the two
    > characters. Any toughts on that!

    Slashes are only valid formatting characters with date formatting characters (e.g., m, dd, yyyy, etc.). To convince yourself of this just try to apply the custom format, 00/00/00, to a 5 or 6-digit integer. Finally, don't confuse date formatting with date entry. When you enter 4/15/2002 or 4-15-2002 Excel is interpeting your input as a date and automatically converting the entered value into a serial date value. This same conversion occurs with =TEXT(A1,"0-00-00")+0; however, its the "+0" that coercing the hyphenated value into a serial value -- not the TEXT function istself.

    [ This Message was edited by: Mark W. on 2002-05-12 12:53 ]

  2. #12
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mark:
    I understand the part regarding Date entry versus true Date value in Excel. Also, no problem regarding the coercion of the the converted text string into a numeric value.

    What I am curious about is why would Excel accept the '-' character and not the '/' -- and as you pointed out, it is the same in Custom formatting numbers as well.

    I surmise that '/' has some special features, even though both characters also happen to be arithmetic operators as well. I also recognize that because both happen to be arithmetic operators has nothing to do with this issue.

    Regards!

  3. #13
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 13:10, Yogi Anand wrote:

    What I am curious about is why would Excel accept the '-' character and not the '/' -- and as you pointed out, it is the same in Custom formatting numbers as well.
    No, a slash isn't the same as a hyphen in custom formats. Look at the error message when you attempt to apply the custom format 00/00/00 to a 5-digit integer. Unlike with date formatting characters slashes are not a valid (built-in) formatting character for use with numeric formatting characters such as 0 and #.

    [ This Message was edited by: Mark W. on 2002-05-12 13:21 ]

  4. #14
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Mark!
    What I am curious about is why would Excel accept the '-' character and not the '/' -- and as you pointed out, it is the same in Custom formatting numbers as well.
    Sorry about the ambiguity in my statement -- what I mean is just as the '-' and '/' don't behave the same way in your formula, they don't behave the same way in the Custom Number Formating either.

    My apologies for any confusion.

    Regards!



    [ This Message was edited by: Yogi Anand on 2002-05-12 17:43 ]

  5. #15
    New Member
    Join Date
    Apr 2002
    Location
    BobH
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks to all that have provided a solution for me. However, I'm afraid I must admit that I can't get any of the formulas to work. Perhaps I'm entering them incorrectly. Do I first select the cell(s) and then Format, Cells, Date and then enter the formula? I did just that with the latest solution and Excel returned an error stating something like the formula could not be handled. This was true with the other solution using TEXT. Since you've both managed to make this work, I'd appreciate your sending me detailed steps so that I can make this work. Thanks for your patience.

  6. #16
    New Member
    Join Date
    Apr 2002
    Location
    BobH
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks to all that have provided a solution for me. However, I'm afraid I must admit that I can't get any of the formulas to work. Perhaps I'm entering them incorrectly. Do I first select the cell(s) and then Format, Cells, Date and then enter the formula? I did just that with the latest solution and Excel returned an error stating something like the formula could not be handled. This was true with the other solution using TEXT. Since you've both managed to make this work, I'd appreciate your sending me detailed steps so that I can make this work. Thanks for your patience.

  7. #17
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Enter =TEXT(A1,"0-00-00")+0 where cell A1 contains a 5 or 6-digit integer representation of a date (e.g., 61592).

    Format the cell containing this formula as...

    mm/dd/yy

  8. #18
    New Member
    Join Date
    May 2002
    Location
    England
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    I have exactly the opposite problem, I am using a combobox within a form to selct from a list of dates however after I have made the selection the number appears in number format not the date format.!!!

    I am sure this is somthing really simple but would be grateful if you could help.

  9. #19
    New Member
    Join Date
    Apr 2002
    Location
    BobH
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark, please forgive me for not understanding this. Please let me know the exact sequence to enter this data. Do you first select FORMAT, CELLS, and then what is the selection from the format options? Where do you enter the =Text formula? I'm sure that I first must highlight/select the cells to format first. Sorry to be so dense about this, and thanks for hanging in there with me.

  10. #20
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-16 14:43, reh37 wrote:
    Mark, please forgive me for not understanding this. Please let me know the exact sequence to enter this data. Do you first select FORMAT, CELLS, and then what is the selection from the format options? Where do you enter the =Text formula? I'm sure that I first must highlight/select the cells to format first. Sorry to be so dense about this, and thanks for hanging in there with me.
    Hi reh37:
    Here is the step by step description.
    1. in cell A1 key in your 5 digit number
    61592
    2. in cell B1 key-in the following
    =TEXT(A1,"0-00-00")+0
    3. Highlite cell B1 and then
    FORMAT|CELL|NUMBER|Date select say 3/4/97
    4. Look at cell B1 -- does it show 6/15/92
    5. If you do see 6/15/92 ... you got it
    6. If you don't see 6/15/92 ... you did not get it
    7. If you got it ... CELEBRATE!; if you did not get it go back to step 1

    Regards!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •