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

Question

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

RE776 (Specifier/Regulator)

(OP)

19 Mar 14 15:08

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 (Structural) · Answer 1 · 2014-03-19 15:45:24

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 (Electrical) · Answer 2 · 2014-03-19 17:49:47

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 (Specifier/Regulator) · Answer 3 · 2014-03-20 04:25:39

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 (Specifier/Regulator) · Answer 4 · 2014-03-20 05:57:49

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

Come Join Us!

Posting Guidelines

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