+ Reply to Thread
Results 1 to 6 of 6

I have the day and month but not the Year-need a macro to calculate the Year

  1. #1
    Registered User
    Join Date
    07-11-2012
    Location
    cali, US
    MS-Off Ver
    Excel 2007+2010
    Posts
    15

    I have the day and month but not the Year-need a macro to calculate the Year

    Hey everyone, i was wondering if this is possible:
    I have a really big spreadsheet with 30 records with the day, the month, and day of the week, but not the year.
    So for example: Monday April 16 but no year.
    The year will be from 2006-2009. So in this case I open up my windows calendar and select april 16th and scroll the years back and I can see that Monday April 16th falls on 2007.

    I was wondering if there is a macro that will give me the year by providing the day, the month, and day of the week and year range of 2006-2009?
    Is this doable or am I out of luck?

    thanks in advance.
    Last edited by iplayball; 07-31-2013 at 01:33 AM. Reason: cuz it's solved

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,832

    Re: I have the day and month but not the Year-need a macro to calculate the Year

    Maybe: (press F9 for a new list)
    Please Login or Register  to view this content.


    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: I have the day and month but not the Year-need a macro to calculate the Year

    Hello iplayball,

    This macro is a Function. It takes the date as "Monday April 16th," and returns "Monday, April 16, 2007". This is the standard Windows long date format. If no matching year is found for the date then the return value is an empty string "".

    Please Login or Register  to view this content.
    EDIT: Added code line to prevent error if no match is made.
    Last edited by Leith Ross; 07-31-2013 at 12:15 AM.

  4. #4
    Registered User
    Join Date
    07-11-2012
    Location
    cali, US
    MS-Off Ver
    Excel 2007+2010
    Posts
    15

    Re: I have the day and month but not the Year-need a macro to calculate the Year

    Thanks protonLeah for your quick response. Please forgive my ignorance. Can you help out with the attached spreadsheet. Attached you will see the format of the dates I'm working with. Can you duplicate the same thing but with this format?

    again, thanks again so much.
    Attached Files Attached Files

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: I have the day and month but not the Year-need a macro to calculate the Year

    Hello iplayball,

    A slight change to the code and it is good to go.

    Please Login or Register  to view this content.
    EDIT: Added workbook with the macro installed.
    Attached Files Attached Files
    Last edited by Leith Ross; 07-31-2013 at 12:46 AM. Reason: Added Attachment

  6. #6
    Registered User
    Join Date
    07-11-2012
    Location
    cali, US
    MS-Off Ver
    Excel 2007+2010
    Posts
    15

    Re: I have the day and month but not the Year-need a macro to calculate the Year

    Leith Ross and protonLeah, u guys are awesome. I went with Leith Ross's code. Can't thank u guys enough.
    sweetness!!

    -shant

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to add Month-on-Month and Year-on-Year %Variance into a pivot table
    By emeritus1812 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-09-2013, 02:37 AM
  2. If Column A Month and Year = ColumnB Todays Month and Year then send email
    By HACCStaff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2013, 12:47 AM
  3. Calculate Year Month and Days and Add them
    By lavan_joy in forum Excel General
    Replies: 8
    Last Post: 11-17-2010, 12:35 PM
  4. to calculate this month, this year, last year figure
    By mingali in forum Excel General
    Replies: 7
    Last Post: 08-07-2010, 03:22 AM
  5. trying to get day/month/year froamt while user enters year only
    By RADIOOZ in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 06-07-2006, 12:35 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1