Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GMT/UTC considerations
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GMT/UTC considerations
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(E60,NOW()+TIME(E6,0,0),IF(E 70,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 "Bernie Deitrick" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GMT/UTC considerations
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GMT/UTC considerations
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GMT/UTC considerations
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GMT/UTC considerations
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" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GMT/UTC considerations
Thank you for all the help. I have it working now. You are the greatest!
Thank you again! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GMT/UTC considerations
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GMT/UTC considerations
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 "ffzeus" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|