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

-----Original Message-----
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
.

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

"MVealey" wrote in message
...
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

Pulling the MOD off the firmula was the trick.

Thanks to all for hteir assistance.

Mike
-----Original Message-----
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
.

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"
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/se...s/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.