You are here:

Excel/compare now() time to a list of timevalues


I want to compare 2 cell values, and when they are equal, then paste a dynamically changing stock price quote into another cell next to the corresponding time. Of the two cells that need to be compared, one is a static number representing time of day and the other is a dynamically changing time value.

I can't use an =if statement because as soon as the if statement is false, it changes the pasted stock quote value to 0, instead of keeping existing value if statement is false.  

Example:  if it is 9:30am, I want to compare the timevalue of 9:30 (which is 0.39583),  with the now() time value that has also been truncated to show only last 5 numbers after decimal. Thus at exactly 9:30am for one second the two cell values will exactly match and then I want to pull the value of cell $A$4 which holds a constantly changing stock price quote into my column B next to the correct time so it can be charted later. How do I capture that stock value when the 2 times match, without it being "erased" as soon as the 2 time valued no longer match a second later?

Hi Ray,

To my knowledge, excel will not dynamically update formulae values on its own unless you have a trigger. I can think of two possible answers:
1. Instead of trying to update the cell by tracking an income feed, you can probably store the feed in a sheet and do a vlookup for the time 9.30. Of course, this would mean your excel is not realtime but with a certain delay.
2. Alternatively, you can have a macro running in loops till it encounters the now() value and then exit. But that would mean the macro has to constantly run and you will not be able to use the excel.

Hope this helps,
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2017 All rights reserved.