Time interval calculation

**snkhan** · 04-29-2023, 11:07 AM

Hi guys,

I get a daily spreadsheet of which I calculate time differences and highlight any time that is over 5mins. I do this manually but the entries can be up to 200s sometimes and I am looking to make a macro so I can automate it. I have tried using the record macro function but the entries are of different length and this doesn't seem to work. I have attached a sample file with details. Please help me out if possible, Please and thank you.

regards,

SN

**alansidman** · 04-29-2023, 11:37 AM

With Power Query
Load your table to the PQ Editor and add index columns

Please Login or Register  to view this content.

Merge the table onto itself and compare times

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

**MrShorty** · 04-29-2023, 01:16 PM

Would you be allowed to use conditional formatting (https://support.microsoft.com/en-us/...b-f1951ff89d7f ). Select column B and tell it to conditionally format all cells larger than 0.00346666 (the serial number fraction of a day that represents 5 minutes) with a red background.