Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date mask (office 97)

    I would like to type 12031956 in a cell and have it 'automatically' format as 12/03/1956 - similar to the
    "Input Mask' in Access. Is this possible?

    Thank you and Happy New Year!
    Michael

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date mask (office 97)

    Excel does not have a way to do that automatically. You could write VBA code and put it into the Worksheet Change Event routine to do that. If you want to do that, we can help but will need a little more information. Do you want the end result to end up in the cell as an Excel date that you can use cell formatting to get the format you want, or do you want it to end up as text?
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date mask (office 97)

    I can see 2 ways one automatic, one after the fact. These are only ideas and NOT worked out, since there are some problems. Note any date < Oct 1, must be entered as text to add the leading zero ('01011957) note apostrophe.

    1) either a macro on the worksheet_change to do it automatically. Something like: (change range as appropriate) [this gets added into the Workshhet code NOT a module]:

    This will require some error checking!! Datevalue will cause runtime error with INVALID dates!!

    You will have to play with the acceptable ranges for values and also add code to add a leading zero.

    "01/01/1900" - "12/31/9999" are acceptable for datevalue but are not all inclusive

    <pre>Private Sub Worksheet_change(ByVal Target As Excel.Range)
    Dim rng As Range
    Set rng = Range("a1:a100")
    If Not Intersect(Target, rng) Is Nothing Then
    Target.Value = DateValue( _
    Application.WorksheetFunction.Replace _
    (Application.WorksheetFunction.Replace(Str(Target. Value), 5, 0, "/"), _
    3, 0, "/"))
    End If
    End Sub
    </pre>


    2) write a function reads this number and changes them: something like:
    =DATEVALUE(REPLACE(REPLACE(B1,5,0,"/"),3,0,"/"))

    then copy and paste special to overwrite

    Steve

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Londonderry, Ireland, Northern
    Posts
    159
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Date mask (office 97)

    A small point compared with Mike's question, but can you get Datevalue to accept 31/12/9999, as distinct from 12/31/9999?

    I'd like to paste in a lot of my own dates which are in alphasortable order yyyymmdd and have Excel do date arithmetic on them - any chance?

    Sydney Harrod, Londonderry, N I.
    All help gratefully received!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date mask (office 97)

    I don't know how datevalue works eith different local settings. It seems to be US based from what I have read, but you would be better getting people who don't use US version to comment.

    If Datevalue expects "mm/dd/yyyy" and you have text "dd/mm/yyyy" in cell A1 you could use something like:

    =DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4))

    to convert to US.

    Steve

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date mask (office 97)

    The DATEVALUE worksheet function seems to recognise local settings.

    The attached worksheet interprets 20030301 correctly as 1 March 2003 (via 01/03/2003) when I run it with Australian settings. I've split the formula between B1 and C1 only for clarity.

    Ian.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date mask (office 97)

    Steve,

    DateValue uses local date settings, so 12081999 will be interpreted as December 8 in the US, but as August 12 in many European countries.

    Your Worksheet_Change function doesn't work for me. The Str function adds a space before the number, so that the slashes end up in the wrong place. If I correct the arguments to Replace, the string is changed to a date correctly the first time, but then the function fires again, causing havoc. Also, the cell format is automatically changed to a date format, so entering a number like 12081999 in a cell that has already been changed causes overflow.

    Your second solution (a formula in another cell) works OK.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date mask (office 97)

    That was part of my point for "error checking".
    It fires once on the original change, then it will fire again since the macro CHANGED the cell. You do NOT want it changed the 2nd time.

    The number/string will have to be evaluated to see if it SHOULD be changed, BEFORE it changes it.

    I had thought about setting the "converted string" as a variable and then testing whether this was a valid date for datevalue. If not, don't change, if so change, but I haven't had a chance to play with it and it was originally to give Michael some ideas, since it was a workaround.

    Steve

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date mask (office 97)

    A few comments about your VBA code. If someone pastes several dates into the range, or someone fills a value down multiple cells in the range, then your code will fix only the value in the active cell. I think that your code needs to disable events while replacing the value in the cell to prevent an endless loop of having the change event triggered by the change in the event routine. I also think that the code should check to see if the cell already contains a date in the event someone edits the date but leaves the slashes in it. And finally, the new value should be checked to see if it is a date before replacing it in the cell. I havent tested the code below, but I think it should be close:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, oCell As Range, strWk As String
    Set rng = Range("a1:a100")
    If Not Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, rng)
    If Not IsDate(oCell.Value) Then
    strWk = DateValue( _
    Application.WorksheetFunction.Replace _
    (Application.WorksheetFunction.Replace(Str(oCell.V alue), 5, 0, "/"), _
    3, 0, "/"))
    If IsDate(strWk) Then
    oCell.Value = strWk
    End If
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

    </pre>

    Legare Coleman

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date mask (office 97)

    Thanks for the input.
    As I said, I put down some ideas, and not complete code. I appreciate you taking the time to finalize it.

    Steve

  11. #11
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date mask (office 97)

    I just got back today and remembered I started a thread. Wow, it really took off!!

    I will try the suggestions at home, and will post back if there are any problems !!

    Thank you all and Happy New Year !!

    Michael

  12. #12
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Date mask (office 97)

    Why not use a style for this? I'm not disagreeing with your VBA answer, but I'm interested in your opinion of one technique vs the other.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date mask (office 97)

    I think that the best you could do with a Style or Cell Format would be to insert the slashes in the display of the value. That will not change it into a Excel Date value that can be used in formula.
    Legare Coleman

Posting Permissions

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