• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Time Zone Changes in Excel

M

MGH27

New Member
Hello,

I am not verse with Excel so bear with me. I am creating a training schedule in Excel 2003 (what my job uses). I created a table where column A is the date; column B is the time; column C-G (merged cells) is the course title, etc. I tried looking at some tutorials to figure out how to note time zones without having to add 4 cells to denote time changes.

What I've done: I went to the Data tab and selected Data Validation. I selected list and listed the 4 time zones, which showed up in dropdown (next to column B).

My dilemma: Was my move feasible? I'm not sure if is possible for me to create a formula to denote hourly difference (in column B) whenever a different time zone is selected from dropdown. I am open to any suggestion or feedback. Thank you.


Mar
 
Days are whole number. Hours are merely a day divided by 24.


So, 7:00 am would be 7/24:


=HOUR(TODAY()+(7/24))


If you get a negative number, the "day" would be yesterday, not today.


Does this get you started, or would you like a more concrete example?


Don
 
Thanks for your response Don. I think that if I can see how it is applied then it would be clearer to me. If you can supply the more concrete example then I would be most gracious.


Mar
 
Firstly, Excel time and date arithmetic is based on the fact that all calendars are actually dates and times offset from a given "start." In Excel, the "start" is January 1, 1900 at midnight. So, if you enter a zero, then convert that to "date" format, you get the "zeroeth" of January, 1900.


Doing everything in numbers makes date and time arithmetic easier to understand. So, let's enter a date and time into a spreadsheet, then convert the column to a format us humans can read.


B1 is a date, formatted in English US, 3/14/01, with a value of 2/25/2012

B2 time, formatted as 13:30


I prefer 24 hour times, but this is just for illustration.


After you enter those values, select the cells, and convert them to general format.


Magically, they become: 40964 and 0.302083


This is how Excel sees the numbers, and this is how you can manipulate them.


So, enter your date in one cell, and format it however you please.

Enter your time in another cell, and format it however you please.


Find your "base" time zone, and convert it to the GMT offset. For example, Central Time is -6.

Find your "target" time zone, and convert it, as well.


Calculate the offset: target - base


Divide the above by 24 (to get hours).


Add the offset to the original time and the original date.


Show the date for the new Date, and time for the new date. Format these as well.


B1 = Base Date to Convert

B2 = Base Time to Convert

B3 is GMT offset for B2 (use a lookup table); use -5 for this example (Eastern)

B4 = Target Time zone GMT offset (Use the same lookup table); use -8/Pacific for this example

B5 is B4-B3 (differential between Target and Base)


B6 is new date formula is =DATE(B1+B2+(B5/24)); format cell as date

B7 is new time =HOUR(B6)&":"&MINUTE(B6); justify right


Does this help?


Don
 
I forgot to mention that you MUST do the date, as well, to account for crossing midnight with one date or the other, but not both.
 
Hi MGH27 & DonMinter,


Sorry for interruption guys, see could this be of any help to you:

http://dl.dropbox.com/u/60644346/Hours_MGH27.xlsx


Simple offset() with Date+Time(x,y,z) function to arrive at required time.


Thanks,

Faseeh
 
Faseeh,


That way certainly works, and with another lookup table and different values, it would fit MGH's request precisely. I am an old programmer, not an Excel expert. I do things as if I were having to do everything, which is why I seem to always be working at the "bit level," where you seem to know Excel's capabilities much better than I do and can make it do the work for you.


Don
 
Hi Don, :)


Unlike you, I am just a simple Textile Engineer. You can understand what a textile engineer has to do with Excel! As you work at bit level with computers, i work with fibers; natural & synthetic! ;) I find using excel a good pass time! I have no plans to compete any professioanl programmer on this forum BTW and u/anyone Likes my way of taking thing that is just enough for me. :D


Thanks,

Faseeh
 
@Don


Can u please upload a sample file that u were working on for this thread. I want to have a look at it.


Thanks
 
Good day Don and Faseeh,

Thank you so very much for the information. This definitely helps. I knew that there was an answer out there, but could not fathom what it could be.

Don, your systematic review of the process definitely provided background rationale as to how and why I would accomplish the task.

Faseeh, your example was also great. I thank you as well.


I am definitely glad that I joined this site. I can finally conquer my "Excel Phobia."


Mar
 
Faseeh,


I sent the file to your email. You're welcome to post a link here. Actually, I don't program for a living; I just used to do a lot of programming. I work in control systems, which is programming, but not for Excel. Sometimes, very rarely, we have to interface with Excel for data collection and display, but that's only done when necessary. We work only in realtime mostly.


Don
 
http://www.4shared.com/file/xNG4i-1q/TimeDateCalculator.html


I think that will download the file I sent to Faseeh. Can someone please confirm that it works (or doesn't)?


Thanks,

Don
 
Don,


Got both your email and 4shared link, both are working!!! Will revert when have a go through your file, Thanks.


Faseeh
 
You must log in or register to reply here.
Back
Top