Calculating time zone differences

  • Thread starter Thread starter MVealey
  • Start date Start date
M

MVealey

I have a project that is going to be installed in
different time zones over the next few months. I have the
install dates in the local time of the install in an Excel
spreadsheet. I'd like to calculate the time zone offsets
so I can plan times my staff has to be available for
coverage of the installations.

I've found how to add or subtract hours from a date with
the following formula...

a1 = 7/15/03 09:00 AM
b1 = 06:00
c1 = formula - =mod(a1+b1,1)
display value - 1/0/00 3:00 PM

The time difference is correct but the date doesn't follow
through even with c1 formatted as mm/dd/yy hh:mm AM/PM

I can do this by hand but I'm going to have just under 200
sites to install.

Help!

TIA,
Mike
 
Hi Mike,

Not sure if you really want to use the mod function.
Check the help files for this function. It's calculating
the remainder for a division calculation: (A1+B1)/1.

Just try adding the values instead: =A1+B1

Cheers,

Mike
 
Hello Mike,

Try this in the UI, and if it does what you want, then you can move it to a
macro.

In cell A6 store the year, i.e. 2003
In cell A7 store the month, i.e. 7
In cell A8 store the day, i.e. 15

Change the formula in C1 to =MOD(A1+B1,1) + DATE(A6, A7,A8)

This should give you the value 07/15/2003 15:00:00

It does for me.
Sender: "MVealey" <[email protected]>
Subject: Calculating time zone differences
Date: Mon, 28 Jul 2003 06:30:36 -0700
I have a project that is going to be installed in
different time zones over the next few months. I have the
install dates in the local time of the install in an Excel
spreadsheet. I'd like to calculate the time zone offsets
so I can plan times my staff has to be available for
coverage of the installations.

I've found how to add or subtract hours from a date with
the following formula...

a1 = 7/15/03 09:00 AM
b1 = 06:00
c1 = formula - =mod(a1+b1,1)
display value - 1/0/00 3:00 PM

The time difference is correct but the date doesn't follow
through even with c1 formatted as mm/dd/yy hh:mm AM/PM

I can do this by hand but I'm going to have just under 200
sites to install.
Click to expand...

Regards,
Chris Jensen[MSFT]

This posting is provided “AS IS” with no warranties, and confers no rights.

Microsoft Security Announcement: Have you installed the patch for Microsoft
Security Bulletin MS03-026? If not Microsoft strongly advises you to
review the information at the following link regarding Microsoft Security
Bulletin MS03-026
http://www.microsoft.com/security/security_bulletins/ms03-026.asp and/or to
visit Windows Update at http://windowsupdate.microsoft.com to install the
patch. Running the SCAN program from the Windows Update site will help to
insure you are current with all security patches, not just MS03-026.
 
Back
Top