View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default GMT/UTC considerations

Curtis,

You need to use the worksheet change event to enter a value into the
date/time cell.

Copy the code below, right-click on the sheet tab, select "View Code", and
paste the code into the window that appears.

It will put the date / time in column F (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.

Obviously, the code can be modified to apply to any range of entered
cells....

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
Target(1, 2).Value = Now() + _
(Worksheets("5 minute").Range("E6").Value - _
Worksheets("5 minute").Range("E7").Value) / 24
Application.EnableEvents = True
End Sub

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