+ Reply to Thread
Results 1 to 3 of 3

Excel / powerquery formula to compare dates/time when other value is matching.

  1. #1
    Registered User
    Join Date
    06-27-2019
    Location
    Antwerp, Belgium
    MS-Off Ver
    2013 at work, 365 at home
    Posts
    1

    Excel / powerquery formula to compare dates/time when other value is matching.

    Hi all,

    I have a list of shipping numbers with piece numbers and date/time of first scan. Now i want to know when there is more then 1 hour between different piece numbers of the same shipping number (partial arrival).

    As i have more then 100k rows i would like to find a formula in PowerQuery to reduce the stress on excel but all ideas are welcome!

    the table should have following outcome.

    Shipping number piece number first scan Partial arrival?
    123456789 2 22/06/2019 13:00 Yes
    456789123 1 21/06/2019 no
    123456789 4 22/06/2019 13:50 Yes
    123456789 3 22/06/2019 14:10 Yes
    456789123 2 21/06/2019 10:50 no
    123456789 1 22/06/2019 14:30 Yes
    987654321 2 22/06/2019 12:10 Yes
    987654321 3 23/06/2019 12:30 Yes
    987654321 1 22/06/2019 12:50 Yes

    I was thinking to combine a min, max function combined with countifs but i cant figure it out
    in addition i am limited to excel 2013 and the datedif function doesnt seem to be included in this version.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,743

    Re: Excel / powerquery formula to compare dates/time when other value is matching.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: Excel / powerquery formula to compare dates/time when other value is matching.

    Rule 03: Cross-posting Without Telling Us

    Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this.

    I have added the crosspost reference for you today. Please comply with this and all our rules in the future

    crossposted: https://www.mrexcel.com/forum/excel-...ml#post5300769
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 11-13-2018, 08:52 PM
  2. [SOLVED] Dates in excel sheet not matching dates in comobox1
    By ColemanJames in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-09-2017, 05:28 PM
  3. Compare dates with time jumps
    By kastell in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-15-2014, 09:28 AM
  4. Replies: 10
    Last Post: 09-04-2013, 08:34 PM
  5. Compare dates & add time for each task done during the day
    By antol in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2012, 11:16 AM
  6. Replies: 5
    Last Post: 02-02-2011, 06:31 PM
  7. Formula to compare time-dates
    By ed ayers315 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-19-2009, 05:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1