Excel - Find Earliest and Latest Times For Each Day - Duel 176 - Episode 1968.5

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!

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

[jicbs]

Is there a Nobel Prize for Excel stuff? You guys deserve it!!!

[mrpennington]

Great podcast, gentlemen! I thought of a slightly different way, as this is similar to a type of problem I work with frequently MAX IFS, or MIN IFS:

{=MIN(IF(DateRange=$G4, IF(ValueRange>0, TimeRange, ""), ""))}
{=MAX(IF(DateRange=$G4, IF(ValueRange>0, TimeRange, ""), ""))}

Array entered, as I'm sure you are aware from the curly brackets, just pointing it out in case anyone looking at this comments wants to try something similar.

Keep up the awesome work guys, this is a fantastic resource you provide. It is appreciated.

[robertlohman]

Good job as always.  Glad you guys are back !

[LearnwithShilpy]

Almost 200 Duels. Was Just Watching Duel 100!

[LearnwithShilpy]

Thank You!! VBA And Macros 2016 Has A Very Good Cover!!

[chrismills]

Hello, thank you for all the free content its excellent, my understanding of excel is limited and im having difficulty creating a helper column, I know the formulas I have used so far could be made significantly more efficient but im unsure how, im now at the stage where the sheet takes a minutes to calculate the formulas, do you offer paid services t0 create or fix excel formulas?

[desiplate]

Using this formula can also work .

Array Formula
=INDEX($D$3:$D$34, LARGE(IF($B$3:$B$34=G4, ROW($B$3:$B$34)-ROW($B$3)+1), 1))

Non Array Formula
=INDEX($D$3:$D$34, AGGREGATE(14, 6, ROW($B$3:$B$34)-ROW($B$3)+1/($B$3:$B$34=G4), 1))

[BillSzysz]

PT can do that directly...but it will be not dynamic :-(