• Time (2000)

    Author
    Topic
    #393806

    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

    Viewing 8 reply threads
    Author
    Replies
    • #716795

      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.

    • #716796

      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.

    • #716949

      Hi Eve,

      What you are trying to do is not easy. For ideas you might like to look at the meeting planner facility at http://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!

    • #716950

      Hi Eve,

      What you are trying to do is not easy. For ideas you might like to look at the meeting planner facility at http://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!

    • #717092

      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.

    • #717093

      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.

    • #717121

      I hate to muddy the waters further but if you check out http://greenwichmeantime.com/local/clock-changes/index.htm%5B/url%5D 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[/url%5D to let everyone know what time it is anywhere in the world.

    • #717154

      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

      Thanks,
      Eve

    • #717155

      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

      Thanks,
      Eve

    Viewing 8 reply threads
    Reply To: Time (2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: