Consulting

Results 1 to 3 of 3

Thread: Solved: Figuring out a DATE from a Number

  1. #1
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location

    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.

  2. #2
    VBAX Contributor
    Joined
    Aug 2006
    Location
    Hampshire, UK
    Posts
    140
    Location
    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.

  3. #3
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location
    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
  •