Date mask (office 97)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

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

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:

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.Value), 5, 0, "/"), _
                3, 0, "/"))
                If IsDate(strWk) Then
                    oCell.Value = strWk
                End If
            End If
        Next oCell
        Application.EnableEvents = True
    End If
End Sub

Reply | Quote

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

Reply | Quote

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

Reply | Quote