INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

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.

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

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.

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

(OP)
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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close