Excel - Find Earliest and Latest Times For Each Day - Duel 176 - Episode 1968.5
2,358 views · Published 12 February 2016 · 11:01 · Indexed 12 November 2026
Channel: MrExcel.com · 2016 · Education
Microsoft Excel Tutorial: Find Earliest and Latest Time For Each Day in Excel Welcome to another episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head-to-head to find the best solution to an Excel problem. In this episode, we tackle the challenge of embedding a helper column in an array formula to find the start and end times for a specific event that occurs daily. The data we are working with is organized from earliest at the bottom to latest at the top, with each day having a specific event. Our goal is to find the start and end times for each event. My solution involves using the IF function to check if the date is equal to the prior date, if the prior value is equal to 0, and if the current value is greater than 0. If all of these conditions are met, the formula returns "first" to indicate the first event of the day. If not, it checks if it is the last event of the day by comparing the date to the cell above, and if the cell above is equal to 0. If these conditions are met, the formula returns "last". Otherwise, it returns "No". This helper column is then concatenated with the date to create a unique identifier for each event. Mike's solution takes a slightly different approach by using the CTRL+Shift+` shortcut to convert the dates to serial numbers. He then creates a helper column that multiplies the serial number date by a boolean logical test to filter out any values that are not greater than 0. This helper column is then used in a LOOKUP formula to find the start and end times for each event. Mike also demonstrates how to simulate the helper column within the formula itself, using the INDEX and MATCH functions. Both solutions are effective in finding the start and end times for each event, but Mike's solution is particularly impressive as it eliminates the need for a helper column altogether. It's great to be back doing Dueling Excel podcasts and we hope you found these solutions helpful. Don't forget to subscribe to our channels for more Excel tips and tricks. See you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Duel: Find earliest and latest time for each day (01:01) Bill's solution using IF function (03:41) Mike's solution using LOOKUP and INDEX functions (05:32) LOOKUP to get last match in excel (10:35) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelduel #excelchallenge #excelformula #excelformulasandfunctions This video answers these common search terms: excel formula to find the earliest date how to get latest date in excel excel formula to find the latest date embed a helper column in an array function LOOKUP 2 to find last match Start with a tricky problem of how to detect the first and last events of a day. Bill uses a helper column with AND. Mike improves with a boolean multiplication, but then explains how to embed the helper column into a formula, both with and without Ctrl+Shift+Enter Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153795/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920