-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules