Solved: Figuring out a DATE from a Number

In Excel if you change 01/01/07 into a number you get: "39083". I want to know if there is a formula that I can figure out the number for a particular date. The reason I am asking is in my program people enter periods for summary reports they enter (Year and Period in two seperate columns) Right now if they enter a month, I enter the numbers for the months in the year of 1900 (Eg: 1 for January, 1 1900). but I want to combine the year and period column, so if someone puts in 2002 January, I want to put a number in that when changed to a Date will come out as 01/01/02 etc.... i don't want to have the users put in the dates because it is harder to understand for them because their actually is no exact dates - I am then creating pivot Tables and grouping by month or quarter or year.

Hi

You could generate a date from your Period and Year using Davevalue:

[vba]myDate = DateValue("1 " & myPeriod & " " & myYear)[/vba]

This assumes that myPeriod is your month (eg "January") and myYear your year (eg 2002). You are building up a string as the argument of the DateValue function (the "1 " at the front specifies the 1st of the month.

Hope this help!

Richard

EDIT: This works if you have them in a different order too:

[vba]myDate = DateValue("2006 January 1")[/vba]

will also provide a valid numeric date.

Thank you,

It is simpler then I figured.