Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Default to Last Year

    I'm making a list in Excel where 1 column contains dates. I want the format mm/dd/yy (no problem yet). However, I'd like to fill out the Date column by putting in just mo/day & have the year automatically fill in as last year (i.e. 2000). Currently, it completes the date as 2001. How can I change this just for selected cells in a column?

    Thanks in advance.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    Phil,

    Excel defaults to the current year if you omit the year when entering a date. If that could be changed (I don't think so) it would only be useful if you applied it to all dates.

    If the date is purely cosmetic, and not used for date calculations you could try the a custom format, I think mm/dd"/2000" would provide what you are looking for. However the underlying value of the date would still be the current year.

    Another possibility would be to insert an additional column with a formula subtracting 365 from the column with the entered date. When all the data is entered you could convert the formulas to values and delete the original column.

    These the only ideas I can come up with just now.

    Andrew C.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    Thanks, Andrew.

    I only use Excel ocassionally (once a year to do helps with taxes). <img src=/S/smile.gif border=0 alt=smile width=15 height=15> The Auto Complete (or whatever it's called in Excel) wants to put 01 after I enter mm/dd & press Enter. I do sort by various columns, inlcuding date, so it's not entirely cosmetic. I was just wishing there was a way to change the default for a single worksheet.

  4. #4
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    Dangerous - but you could just change your system clock back to 2000 while you work on that one sheet...

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    This is a little bit more complicated and I don't know if it is worth the pain, but you can do it using the worksheet_change event of the worksheet where you want to enter these dates. The code below should be placed in the worksheet module.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim d As Integer
    Dim m As Integer
    Dim ss As Date
    Dim R As Range
    Dim IntersectR As Range
    Set R = Range("A:A")
    Set IntersectR = Application.Intersect(R, Target)
    If Not IntersectR Is Nothing Then
    d = Day(IntersectR.Value)
    m = Month(IntersectR.Value)
    ss = d & "/" & m & "/" & "2000"
    Application.EnableEvents = False
    IntersectR.Value = ss
    IntersectR.NumberFormat = "dd/mm/yyyy"
    Application.EnableEvents = True
    End If
    End Sub

    As an example, I assumed that your dates are entered in column A of the worksheet. If you enter something like 20/03 in a cell in column A, Excel takes 2001 as the default year. I am deriving the day and month from your input (which has 2001 as the year by default) and change the year to 2000 and display your date with 2000 as the year. Now, the full date has 2000 as the year and no longer 2001. Of course, if you want to enter a date with a year differently from 2001, the code will change that to 2000. With some if ... then statements, you can filter out only those dates with 2001 as the year. Excuse me for the European date formats.

  6. #6
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    If you don't want to go as far as implimenting Hans' solution, Find & Replace would probably to the job.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    Hi Hans:

    Thanks for the macro. I tried it, switching the mm & dd & switching 2000 to 00. It works most of the time. If I type, 12/22 & press enter, it works. If I press 12/22/ & press enter I get an error--& it then switches to 12/30/00. I can't delete that value either; it keeps coming back (like the Jason tab in Word).

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    <P ID="edit"><FONT SIZE=-1>Edited by Phil Rabichow on 01/03/25 09:50.</FONT></P>Hi Lief:

    Yea, I thought of that. But then other files will get the wrong access or creation date; probably wouldn't hurt though. You'd think that Excel would allow you to create a default year for a worksheet; I'm sure there are accountants who use it. Thanks for the reply; I appreciate it.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    Now that's a good idea--& simple too. I'd just have to pretend that all dates were current until the end. I wonder why I didn't think of that before. Too emotionally involved, I suppose. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  10. #10
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    If you didn't want to confuse yourself whilst entering the data, just format the cells for mm/dd until you're finished.

    I have found that Excel is relatively amenable to date-data entry. For example, entering 25-3 will give you 25-Mar-2001 - i.e. you dont need to enter 2 digits for the month. Likewise, entering 25-3-0 will give me 25-Mar-2000.
    If your formatting uses '/' dividers, I think you can still enter the date using a '-' and vice-versa.

    BTW - I think subracting 365 from the date may only work back to March 1st last year - wasn't 2000 a leap year? To make any of the solutions work, you may have to manually enter any entries for 29-Feb-2000 in full.... <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    It is clear that my solution is far more complex than the one proposed by Leif. I agree that the best solution is always the most simple one. But, to answer your question, I added some code to check if your entry is a date. If not, the entry is undone and a messagebox comes up to warn you that you did not enter a date.
    It gives you a very good idea of what you can do with this event if you are considering data validation rules which cannot be programmed via the Excel Data >> Validation menu.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim d As Integer
    Dim m As Integer
    Dim ss As Date
    Dim R As Range
    Dim IntersectR As Range
    Set R = Range("A:A")
    Set IntersectR = Application.Intersect(R, Target)
    If Not IntersectR Is Nothing Then
    If IsDate(IntersectR.Value) Then
    d = Day(IntersectR.Value)
    m = Month(IntersectR.Value)
    ss = d & "/" & m & "/" & "2000"
    Application.EnableEvents = False
    IntersectR.Value = ss
    IntersectR.NumberFormat = "dd/mm/yyyy"
    Application.EnableEvents = True
    Else
    Application.EnableEvents = False
    Application.Undo
    MsgBox "This is not a date"
    Application.EnableEvents = True
    End If
    End If
    End Sub

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    If you use the Date() function in the cell you want to show your proper date you can put Date($X$1,y1,z1). You then insert the year you want in x1 which is the only cell you need for the year and create two extra columns in your sheet y and z, say, where you insert the month no and day no. Hey presto! the date you want appears in the appropriate cell.

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    Thank you so much, Hans. You're solution, while more unforgiving than Lief's (if I accidentally enter m/d/ as opposed to m/d, I get an error message), works. Now I've got at least 2 ways to do this.

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Last Year - Thanks

    Thanks, Herworth. Always nice to have alternatives. I'm set to go (of course, taxes are done this year, thank goodness).

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Default to Last Year

    Phil,

    A slight modification to Hans's code will auto correct your tendancy to add an extra "/". I hope (a) Hans does not mind, and ([img]/forums/images/smilies/cool.gif[/img] it works for you. Why should you have to change your habits to suit Excel. Amendments in red.
    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim d As Integer
    Dim m As Integer
    Dim ss As Date
    Dim R As Range
    Dim IntersectR As Range
    Set R = Range("A:A")
    Set IntersectR = Application.Intersect(R, Target)
    If Not IntersectR Is Nothing Then<font color=red>
    On Error Resume Next
    If Right((IntersectR.Value), 1) = "/" Then
    IntersectR.Value = Left(IntersectR.Value, (Len(IntersectR.Value)) - 1)
    End If
    On Error Goto 0</font color=red>
    If IsDate(IntersectR.Value) Then
    d = Day(IntersectR.Value)
    m = Month(IntersectR.Value)
    ss = d & "/" & m & "/" & "2000"
    Application.EnableEvents = False
    IntersectR.Value = ss
    IntersectR.NumberFormat = "dd/mm/yyyy"
    Application.EnableEvents = True
    Else
    Application.EnableEvents = False
    Application.Undo
    MsgBox "This is not a date"
    Application.EnableEvents = True
    End If
    End If
    End Sub
    </pre>


    Andrew

Page 1 of 2 12 LastLast

Posting Permissions

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