Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Display Different year from current

    If you type in just the month and day, for instance, 3/15, Excel adds the current year.

    I am doing a (huge) list that involves a previous year. Is there any way I can get Excel to fill in the proper year if I only add the month and day. (My current way of doing it is to do the month and day, and then when all is done, I do a search for /2012 and replace it with /2010. It works, and is not too much trouble except when I miss some of the substitutions.

    Just asking.
    [I have been here for years; I had to get things restarted]

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    The only way I know is by changing your system clock !

    That will work, but may give some problems with, for instance, your AV program - at last until you change it back.

  3. #3
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MartinM View Post
    The only way I know is by changing your system clock !

    That will work, but may give some problems with, for instance, your AV program - at last until you change it back.
    Giving Troubles it does (with apologies to Yoda). I just thought I would ask in case someone had stumbled upon something.
    [I have been here for years; I had to get things restarted]

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 987 Times in 917 Posts
    Add an extra column that calculates the year and use that to check you have changed everything.
    You could use the extra column to subtract 365 days from the value and then copy / paste special, values.

    cheers, Paul

  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
    For entering many dates, I often use 3 intermediate columns: month, day, year and then use date function to calculate it. After entry I paste - special- values.

    Steve

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    You could use a worksheet change event to set the required year.

    The following code checks for entries made in column 2 only (adjust code as required).
    The following code sets the year to 2010. (adjust code as required).

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    zEntry = Target.Value
    If IsDate(zEntry) Then
    Application.EnableEvents = False
    zAddress = Target.Cells(1).Address
    zDay = Day(zEntry)
    zMonth = Month(zEntry)
    zYear = 2010
    Range(zAddress).Value = DateSerial(zYear, zMonth, zDay)
    End If
    Application.EnableEvents = True
    End Sub

    zeddy

Posting Permissions

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