GMT/UTC considerations

[ffzeus]

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'!E6>0,NOW()+TIME('5 minute'!E6,0,0),IF('5
minute'!E7>0,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

 

[Bernie Deitrick]

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]

Hi Bernie,

Thank you for the reply. I copied and pasted the code you gave here where
you suggested. From reading your reply I wish I knew coding. Unfortunately
my time field still recalculates everytime a new entry is made in any cell in
the "E" column. I know it has to do with my use of the NOW() funtion.

=IF(E10=0,"--",IF(E6+E7=0,NOW(),IF(E6>0,NOW()+TIME(E6,0,0),IF(E7>0,NOW()-TIME(E7,0,0)))))

Here is the exact code from the "Time" cell I am trying to populate. It
works great the first time around. I even have my date cell showing tomorrows
date if the time crosses midnight. What I need it to do is populate with
that info in both cells which are B10:B44 for the "Date" column and C10:C44
for the "Time" column and then not recalculate them again unless the entry in
the E column for that row is removed and reentered.

Thank you for your time and help. I will keep plugging at it.

Curtis Z

 

[ffzeus]

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

 

[Bernie Deitrick]

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]

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

 

[ffzeus]

Yes, it is a macro and yes I am my worst enemy. Thank you for the help. Which
lines affect where the output goes? It si populating the date in column A and
teh time in column B and I would like to make that B and C. I am assuming it
is the With Target (1, -3) and With Target(1, -2) lines?

Thank you again.

 

[ffzeus]

Thank you for all the help. I have it working now. You are the greatest!
Thank you again!

 

[ffzeus]

Hi Bernie,

Sorry to bother again, I noticed that the day field does not add a day if
the time crosses midnight with the addition of the GMT offset from E6 and E7.
Can you tell me how to add this?

Thank you.

Curtis Z

 

[Bernie Deitrick]

Curtis,

It adds a day for me. With an 8 in cell E6 of "5 minute", I get
Jan 8, 2006 and 00:46:10
Which is tomorrow, 8 hours from now (now being Jan 7, 2006, 16:46:10).

You might want to make sure that there is only one value filled in E6 _or_
E7.

HTH,
Bernie
MS Excel MVP