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

[iplayball]

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.

[protonLeah]

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

[Leith Ross]

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.

[iplayball]

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.

[Leith Ross]

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.

[iplayball]

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

-shant