I have a data set with a list of dates and times in m/d/yyyy h:mm format. I am looking for an Excel formula to find the earliest and latest time by day. I am using Excel 2016 and do not have the Data Analysis Toolpak. This is a privacy protected data set, so I have no workbook to share. I have tried using MIN, IF, and AND functions below but always get 1/1/1900 12:00 as the result.
Example: Find earliest time on 12/1/2021
A$2:A$100 - Range of dates and times from 12/1/2021-12/10/2021
B2 - Date1, eg. 12/1/2021
B3 - Date2, eg. 12/2/2021
=MIN(IF(AND(A$2:A$100<B3,A$2:A$1000>B2),$A$2:$A$100))
=SMALL((A$2:A$100<B3,A$2:A$100>B2),1)
I have an additional need to find the earliest time on a date for a specific place when there is an additional column with different place names. Thank you for any help with this!
Here is a Power Query Solution
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Column1", type text}}, "en-US"), "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
#"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Column1.1"}, {{"Earliest", each List.Min([Column1.2]), type nullable text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Column1.1", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Column1.1", Order.Ascending}})
in
#"Sorted Rows"
You can review in the attached file.
Hello,
For a formula based solution it is of course easier if you split up the dates and times to helper columns, then you easily can use the MINIFS function to get your result.
See attached file.
Br,
Anders
Hi Jason
How about a PivotTable solution?
I grouped the Dates and then using Max and Min for the Values and some formatting to the entire PT.
Please refer attachment using Anders's data.
Thanks Anders for the data!!
Regards
Sunny
