Excel Forum

Earliest and Latest Time by Day (and Place)

Jason Huberty

New Member

Members

Forum Posts: 1

Member Since:
December 9, 2021

Offline

December 9, 2021 - 1:11 am

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!

See Answer

Alan Sidman

Steamboat Springs, CO

Member

Members

Trusted Members

Forum Posts: 205

Member Since:
October 18, 2018

Offline

December 9, 2021 - 5:28 am

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.

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 929

Member Since:
December 7, 2016

Offline

December 9, 2021 - 6:57 am

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