Need help on VBA, Finding matching dates (YYYYDDMMHHMM) of two excel documents and extra data

[RE776]

I have two spread sheets, sheet 1 contains a 2 x 4213 table, [column A = time in the format of YYYYDDMM HHMMSS; Column B are numerical data]. Sheet 2 contains 9 x 6560 table [Column A = time in the format of YYYYDDMM HHMMSS; Column B - I are numerical data].

I want to use some sort of array approach, using the time (column A) in sheet , to find a matching time in sheet 2, then extra the values between columns B to I into the same row of the time in sheet 1 I just used to find the values.

You might immediate realised that 2347 values will not find a "home" in sheet1, but this is fine.

The area that I am really struggling is the time will never be the same in ss level. All the times are at 1 minute intervals, even though they might have the same year, date, month, hour and minute. It is almost certain that the seconds will be different. I need to somehow reset the "ss" part of the times in both sheets to 00.

Could anyone give me had on this?

I have tried everything but with no luck.

Thanks in advance

Felix

 

[Denial]

The following formula will "round" the date/time down to the nearest minute
=A3-MOD(A3,1/(24*60))

Otherwise you need some sort of criterion to decide whether the two times are "close enough" for you to be happy to assume that they are the same.[ ] For example
=if(abs(time1-time2)<1.5/(24*60),"The same times","Different times")
will treat times within 1½ minutes of eachother as being the same.

Neither of the above fully answers your query, but might help you find a way.

 

[davidbeach]

If it is really there as a text string, rather than a time number just displayed as YYYYDDMM HHMMSS, you can always just look at the leftmost 13 characters.

 

[RE776]

Thanks for the reply, The current dates are defined under the catergory of customer, as "dd/mm/yyyy hh:mm" type, e.g. 15/11/2014 04:49. Although seconds aren't being displayed yet, when I tried to compare two dates, they still look at the seconds as well.

I shall have a go at your formula Denial,

Cheers!

Felix

 

[RE776]

Thanks for the help.

=A3-MOD(A3,1/(24*60)) worked perfectly. I used Index and match to extra the data at the end. Thanks for your time.

Much appreciated!

Felix