Results 1 to 6 of 6

Thread: Time (2000)

  1. #1
    New Lounger
    Join Date
    Oct 2001
    Location
    Northern VA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time (2000)

    I work for a global company and I've been asked to add some information to a directory.

    The current file shows Name, Region, Country, Office, Phone Number, etc.

    I've been asked to add in the time difference between that person and us (either current user or just Eastern US time), and then the timeframe in Eastern US that would be ok to call the person on the list (to help coordinate conference calls, so like 8am-6pm for everyone).

    I've gathered the GMT for each of the people (US, Latin America, Europe and Asia) and whether or not they participate in any kind of Daylight Savings Time. Unfortunately, this is all complicated by the fact that not each country participates and not at the same time (Europe is different from US, then Southern Hemisphere is different, and Brazil is also different). Plus the dates are usually "Last Sunday in March" kind of thing, so it changes from year to year.

    I'm having trouble figuring out the best way to organize all this information. First, is there a way to get Excel to spit out the date for "Last Sunday in March"? Any thoughts on how to best organize this? I don't know if it will be distributed to the people on the list, or just used by the "organizers" of the team. Any other thoughts on the project?

    Many thanks.

    Eve

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time (2000)

    Eve,

    This is something just slapped together. Attached is a single sheet workbook. You enter the desired year, month, week number, and the day of week (Sunday=1...Saturday=7). Press the Date button and the date for the given values is returned. This is something for you to toy with. The function is from Chip Pearson which I modified the arguments slightly so that they are easier to read.

    To view the code, press the Alt+F11 keys.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Time (2000)

    Hi Eve,

    What you are trying to do is not easy. For ideas you might like to look at the meeting planner facility at www.timeanddate.com which addresses your problem with up to four people in different time zones. As notbrl has done, you should also checkout Chip Pearson's site http://www.cpearson.com/excel/topic.htm where Chip has literally heaps of stuff on dates and times etc. Also Chip has a calendar under downloads which is worth checking out as it shows you how to setup holiday dates etc. including what notbrl has indicated.

    I presume that you will need to supply this directory around the world, so at each location it has to work by the location being identified and the time offsets for all the locations being based on where the user is located. I would be setting up a lookup table where these offsets are calculated on a separate sheet, and then the directory locating the time offset by the person's location and applying it to identify matching working hours with the location where the user is.

    Good Luck!
    Peter Moran
    Two heads are always better than one!

  4. #4
    Lounger
    Join Date
    Nov 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time (2000)

    The attached file willl calculate any last day for any month in any year. All that is required is 3 inputs and I have included 3 different calculations that each handle the year input in a different way.

    The formula that calculates the date of the last day is rather complex formula. Therefore I have also given a break down of the component formulae to help you understand how it works.

    Hope this is of some use.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time (2000)

    I hate to muddy the waters further but if you check out http://greenwichmeantime.com/local/c...nges/index.htm you'll see that even the areas that have daylight savings time do not change their clocks on the same day.
    You might want to consider a free download like http://www.timezonesforpcs.com to let everyone know what time it is anywhere in the world.

  6. #6
    New Lounger
    Join Date
    Oct 2001
    Location
    Northern VA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time (2000)

    Thank you everyone for your responses. I did check out Chip's page, and found elements that could work, but wanted to come here first to see what you guys thought about the project. If some of you think this is a little difficult then I have my work cut out for me ;-)

    I'll check out everything suggested here, but may come back with more questions [img]/forums/images/smilies/smile.gif[/img]

    Thanks,
    Eve

Posting Permissions

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