Hi,
I have two excel data files that have a DateTime column in the following format
dd/mm/yyyy hh:mm:ss
What I want to do is take the first DateTime value in file one and find the closest time in file two and then copy some data from file two to file one. All the copying etc is no problem, my mental block is how to find the closest time.
Some explanation. The DateTime fields are from two separate instruments. In file one each DateTime is approximately 3 minutes apart and they could have any number of seconds e.g.
10/10/2000 14:12:22
File two is from a data logging system that should log on the even minute, it doesn't. It can be up to 1 second out either way so we get
10/10/2000 14:11:59 or
10/10/2000 14:12:00 or
10/10/2000 14:12:01
I have written a simple routine to take the DateTime from the file one and produce a DateTime with even seconds to the closest minute, so for the example given above where the seconds are 22 the corrected DateTime is 10/10/2000 14:12:00, if the seconds were 30 or greater it would have been 10/10/2000 14:13:00. Now I can do my comparison but because the time in file two can be out by 1 second the comparison may fail. I could do three comparisons but that seems ugly, could also convert the DateTime in file two to be an equal minute but I was hoping for a simpler solution.
If you can understand this, any ideas.
George
I have two excel data files that have a DateTime column in the following format
dd/mm/yyyy hh:mm:ss
What I want to do is take the first DateTime value in file one and find the closest time in file two and then copy some data from file two to file one. All the copying etc is no problem, my mental block is how to find the closest time.
Some explanation. The DateTime fields are from two separate instruments. In file one each DateTime is approximately 3 minutes apart and they could have any number of seconds e.g.
10/10/2000 14:12:22
File two is from a data logging system that should log on the even minute, it doesn't. It can be up to 1 second out either way so we get
10/10/2000 14:11:59 or
10/10/2000 14:12:00 or
10/10/2000 14:12:01
I have written a simple routine to take the DateTime from the file one and produce a DateTime with even seconds to the closest minute, so for the example given above where the seconds are 22 the corrected DateTime is 10/10/2000 14:12:00, if the seconds were 30 or greater it would have been 10/10/2000 14:13:00. Now I can do my comparison but because the time in file two can be out by 1 second the comparison may fail. I could do three comparisons but that seems ugly, could also convert the DateTime in file two to be an equal minute but I was hoping for a simpler solution.
If you can understand this, any ideas.
George
