Thank you agian Bernie.

I did copy and paste the code exactly as it show here, by right clicking the
worksheet tab, selecting "View Code", and pasting it in the right hand pane.
I then wnet back out to the spread sheet and deleted the formulas from colums
B and C entriely.

When I make an entry in an E cell nothing populates in B or C cells at all.
I am guessing I am still missing something? I do get an alert when I open
worksheets saying my security is to high and no macros will run, but this is
not a macro I believe.

Sorry to keep pestering with this. I really want to understand and make it
work though.

Thank you.

Curtis Z

"Bernie Deitrick" wrote:

Curtis,

Sorry for not being more clear. You need to NOT use formulas - formulas
don't work the way theat you want them too. You need to use _only_ the
worksheet change event to enter a value into the date and time cells.

Copy the code below, right-click on the sheet tab, select "View Code", and
paste the code into the window that appears. Remove any formulas from
columns A and B that you have written - the ones using the NOW() function.

This will put the date in column A and the time in column B (in the same
row) for any change in column E, if the change is done to a single cell and
not to a group of
cells, adjusted for the time zone changes. It uses time math without using
time functions.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 5 Then Exit Sub
Application.EnableEvents = False
With Target(1, -3)
.Value = Int(Now() + _
(Worksheets("5 minute").Range("E6").Value - _
Worksheets("5 minute").Range("E7").Value) / 24)
.NumberFormat = "mmm dd, yyyy"
End With
With Target(1, -2)
.Value = Now() + _
(Worksheets("5 minute").Range("E6").Value - _
Worksheets("5 minute").Range("E7").Value) / 24 - _
Int(Now() + (Worksheets("5 minute").Range("E6").Value - _
Worksheets("5 minute").Range("E7").Value) / 24)
.NumberFormat = "hh:mm:ss"
End With
Application.EnableEvents = True
End Sub

"ffzeus" wrote in message
...
Maybe I am going about this in a too complicated manner. Basically what I
am
trying to do is have the spreadsheet populate the date (B10:B44) and time
(C10:C44) fields with UTC/GMT, regardless of the users time zone, when the
cell in the E column in that row is populated. Would gladly take
suggestions
on how to make it do this in an easier manner.

Curtis Z

"ffzeus" wrote:

I am attempting to create a spread sheet that will compensate for time
zone
differences between the reference point (GMT) and teh user location. I
have
come up with a way to do so by creating two user inpput fields for Ahead
of
GMT and Behind GMT. My formula calculates the teh exact moment they enter
the
desired information and even adds the day if it crosses midnight GMT. The
problem I am having is I use the NOW() funtion to grab the date/time of
the
moment of entry. Each subsequent entry on any other field that uses the
NOW
function recalculates any entry int the entire workbook that uses the NOW
function. Is there a way to prevent a cell that has already populated
from
recalculating when another cell is entered? Code example:

=IF(E6=0,"--",IF('5 minute'!E60,NOW()+TIME('5 minute'!E6,0,0),IF('5
minute'!E70,NOW()-TIME('5 minute'!E7,0,0))))

Where E6, E7, E8, etc. is the field that requires imput, '5 minute'!E6 is
the Hours Behind GMT static entry, and '5 minute'!E7 is the static input
Hours Ahead of GMT.

Any suggestions?

Thank you.
Curtis Z