Results 1 to 2 of 2
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Springfield, Ohio, USA
    Thanked 2 Times in 2 Posts

    Date/Time (XL97)

    Is there an option to force Excel to keep dates/times in the format in which I enter them?

    If I enter "19-Feb-03", Bill leaves it alone.
    If I enter "22:30:15", Bill leaves it alone.
    If I enter "19-Feb-03 22:30:15", Bill is sure that I really want to see "2/19/03 22:15", but I DON'T <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

    Note, I know that I can format this however I want, but it is not that simple: the cell is being filled by VBA based on two fields that the user selects. These two fields could be anything.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 465 Times in 382 Posts

    Re: Date/Time (XL97)

    Hi Sam,

    I don't think you change change Excel's default behaviour to achieve what you want. Should be easy enough to test the fill data and format the target cell via VBA though. If the format of the target cell is dependent on the format of the source cells and is supposed to combine their formats, perhaps you could read & concatenate their formats for the target cell (eg assuming A1 has dates and B1 has times and the result goes into C1, then a cell formatting logic along the lines of:
    if (A1="",C1Format =B1Format,if(B1="",C1Format =A1Format,C1Format =A1Format & " " & B1Format))
    might do.


    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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