Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Virtual time line (All)

    It is not uncommon for an app to need to give the user a means to specify points on a virtual time line, i.e., the line starts at 0 and the points are labelled as offsets in terms of weeks, months, days, years, or some combination of those terms.

    Of course, there are at least the following problems:

    1. How many days are there in a month?

    If I assume 30, then a year would have to be used as having 360 days.

    OK, I can get by this point by assuming things.

    2. But what about international issues?

    If I use an interface that allows the user to specify offsets using English words or abbreviations, that does not work well internationally.

    What conventions are used?

    Is there a book that discusses such conventions?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Virtual time line (All)

    Problem 1 depends on what YOU want to happen. One way of solving problem 1 is, if the offset is in months, then add/subtract months not days. If the starting date is in A1, then you can use a formula like this in B1:

    <pre>=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
    </pre>


    However, when you do this, and A1=1/31/2002, then B1=3/3/2002. You have to define what a month is for your application, then write formula/code to implement that definition.

    As far as problem 2 is concerned, again you will have to create separate workbooks, or program to handle the international issues for the national languages you want to support. There are ISO standards for specifying dates, but those probably will not be of much help for what you are describing.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Virtual time line (All)

    Lemme clarify.

    For problem 1, all cells represent offsets, not dates, so there is no date in cell A1.
    However, each offset is in terms such as:

    "1 year, 3 months"
    "23 weeks, 4 daze"

    But these are all relative to a virtual time line that starts at 0.

    If I assume that time 0 is the current date, then the result would change depending on when the function ran.
    One solution may be to select an arbitrary date to represent 0 and always use that date as point 0.

    If the user wants to use real dates, the user could enter a real date for point 0.

    The solution to the 2nd problem may be to use different strings within the code, depending on the Office/Windows language code, and/or, allow the user to choose, or set the values when the code is installed, but I'd much rather compile a Select Case statement to choose the strings.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Virtual time line (All)

    Ok, if these are "virtual" times, then you can define a month as 365/12 or 30.41667 days. Set the format to display no decimals, and you will get a year of 365 days. 1 years 3 months would be 15 times 30.41667. 23 weeks and 4 days would be ((23*7)+4. Again, you have to define how you want it to work, and then set it up to work that way.
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Virtual time line (All)

    Howard

    I suspect that when all is said and done the Microsoft Date functions implement the requirements (including international) very well. <img src=/S/duck.gif border=0 alt=duck width=23 height=23>

    As defined, the date format is a virtual timeline from an arbitrary zero point and increases by one for each day.
    When it is formatted as a date it has the benefit that it can already translate to international formats. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    To get it to have a different zero point only requires the subtraction of the datevalue for any arbitrary date such as NOW() and the use of a format that is non-date - e,g, fixed

    If you wish to have it represent other units such as months - then divide by 30 if you must - but there is a perfectly good MONTH() function that could do the job accurately. Ditto for other intervals.

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Virtual time line (All)

    Yes, and that's what I have suggested as a solution, but the requirement is that users need not specify dates, rather they also need to be able to specify, e.g.:

    1 year and 4 months

    Without specifying a date.

    The issues include:

    1. Locale dependent terms for "years", "months", "weeks", "days". This can be handled with a Select Case within the code, subject to the ability to enter non-Latin characters as strings in the VBA code.

    2. How many days in a month, in a year? It would seem that this is easiest solved by picking a fixed date for point 0. If the user wants a real date, the user would supply a real date for point 0.

Posting Permissions

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