Calculating time zone differences

[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

 

[Mike Scobie]

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

 

[Cecilkumara Fernando]

MVealey,
=a1+b1
why you cant use the above formula in C1
Cecil

 

[MVealey]

Pulling the MOD off the firmula was the trick.

Thanks to all for hteir assistance.

Mike

 

[Chris Jensen [MSFT]]

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.

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.