Return the latest date

[kyddrivers]

I have an import from SQL that gives me a Run Date Time column. I am trying to return the latest (most recent) value, so I can see at a glance what it is. I have tried an INT(A2) or Max(A2:A14) and I am getting errors in return. Thanks in advance for your suggestions!

Book1
	B
1	RunDateTime
2	2022-08-29 19:00:10.1802823 -07:00
3	2022-11-23 19:03:17.1341212 -08:00
4	2022-12-07 19:02:44.9223430 -08:00
5	2022-11-23 19:04:42.4532233 -08:00
6	2022-12-07 19:05:00.6779341 -08:00
7	2022-12-21 09:17:20.8600203 -08:00
8	2022-12-29 13:51:34.1732389 -08:00
9	2022-12-29 13:09:23.3498327 -08:00
10	2022-12-21 09:31:17.5302483 -08:00
11	2022-12-21 09:34:37.4441480 -08:00
12	2022-11-08 09:25:24.8650237 -08:00
13	2022-09-29 14:25:18.5195997 -07:00
14	2022-12-21 09:36:04.5257032 -08:00
Sheet1

 

[Micron]

Maybe =MAX(DATEVALUE((LEFT(A2:A14,10))))

Make sure formula cell is formatted as date.

 

[kyddrivers]

Thanks for the reply Micron! That would work if I only needed the date. Lines 8 & 9 are both 12/29/2022, I need the time portion as well, so I can match up the values related to the run that took place at 2022-12-29 13:51:34.1732389 -08:00 vs. the run that happened at 2022-12-29 13:09:23.3498327 -08:00

 

[Micron]

Sorry, missed that fact. Easily done in vba I would think. I can't find any formula based function that will convert dates with times when they're both strings, but really, Excel formulas are not my thing. I'd probably convert the string portions to numbers, add them together, then compare the numbers. The numbers would look like this
44935.6632986111
44935.663275463
Those are two date time values for Now() but a second or so apart. Easy enough to see which is greater like that. Maybe a formula wizard will chime in for you.

 

[kyddrivers]

I was able to piece this together to make it work. I am using a helper column of text to columns and pulling in the first 16 characters into Column C from B, in C1 Text(Max( with formatting to match Column B. In column D, use an If c1=Left(b2,16) Y, N). Then, in cell D1, use Xlookup to return the full value from Column B if there is Y. It's not pretty or sexy, and it works for these 15 rows of data; my concern is what happens to excel when I have 75K rows of data to play with?

Can anyone think of a better way to make this happen?

Thanks!

P.S. I am working with the DB to see if we can limit the size of the data dump in the future, but for now, it is what it is.

Book1
	B	C	D
1	RunDateTime	2022-12-29 13:51	2022-12-29 13:51:34.1732389 -08:00
2	2022-08-29 19:00:10.1802823 -07:00	8/29/2022 19:00	N
3	2022-11-23 19:03:17.1341212 -08:00	11/23/2022 19:03	N
4	2022-12-07 19:02:44.9223430 -08:00	12/7/2022 19:02	N
5	2022-11-23 19:04:42.4532233 -08:00	11/23/2022 19:04	N
6	2022-12-07 19:05:00.6779341 -08:00	12/7/2022 19:05	N
7	2022-12-21 09:17:20.8600203 -08:00	12/21/2022 9:17	N
8	2022-12-29 13:51:34.1732389 -08:00	12/29/2022 13:51	Y
9	2022-12-29 13:09:23.3498327 -08:00	12/29/2022 13:09	N
10	2022-12-21 09:31:17.5302483 -08:00	12/21/2022 9:31	N
11	2022-12-21 09:34:37.4441480 -08:00	12/21/2022 9:34	N
12	2022-11-08 09:25:24.8650237 -08:00	11/8/2022 9:25	N
13	2022-09-29 14:25:18.5195997 -07:00	9/29/2022 14:25	N
14	2022-12-21 09:36:04.5257032 -08:00	12/21/2022 9:36	N
15	2022-12-29 13:51:34.1732389 -08:00	12/29/2022 13:51	Y
Sheet1

Cell Formulas
Range	Formula
C1	C1	=TEXT(MAX(C2:C15), "yyyy-mm-dd h:mm")
D1	D1	=XLOOKUP("Y",D2:D15,B2:B15)
D2:D15	D2	=IF($C$1=LEFT(B2,16), "Y","N")

 

[Micron]

using a helper column of text to columns

Interesting. I thought of that and totally forgot to mention it. Too many other options on my mind I guess.
Using your data, I split at 19 characters, custom formatted the range as mm/dd/yyyy hh:mm:ss and used Max only. No other functions needed. I would not use Text function as it just compounds the problem. The dates that are split off below are actual date data type values. If your data could be identical down to the second, I would use 27 (? I think) for the split, then you'd include what looks like fractions of a second to me.

1​	RunDateTime
2​	2022-08-29 19:00:10.1802823 -07:00	08/29/2022 19:00:10​	12/29/2022 13:51:34​	=MAX(C8:C9)
3​	2022-11-23 19:03:17.1341212 -08:00	11/23/2022 19:03:17​	seems right
4​	2022-12-07 19:02:44.9223430 -08:00	12/07/2022 19:02:45​
5​	2022-11-23 19:04:42.4532233 -08:00	11/23/2022 19:04:42​
6​	2022-12-07 19:05:00.6779341 -08:00	12/07/2022 19:05:01​
7​	2022-12-21 09:17:20.8600203 -08:00	12/21/2022 09:17:21​
8​	2022-12-29 13:51:34.1732389 -08:00	12/29/2022 13:51:34​
9​	2022-12-29 13:09:23.3498327 -08:00	12/29/2022 13:09:23​
10​	2022-12-21 09:31:17.5302483 -08:00	12/21/2022 09:31:18​
11​	2022-12-21 09:34:37.4441480 -08:00	12/21/2022 09:34:37​
12​	2022-11-08 09:25:24.8650237 -08:00	11/08/2022 09:25:25​
13​	2022-09-29 14:25:18.5195997 -07:00	09/29/2022 14:25:19​
14​	2022-12-21 09:36:04.5257032 -08:00	12/21/2022 09:36:05​

 

[Dave Patton]

Maxif.xlsm
	A	B
1	RunDateTime	29-Dec-22 13:51
2	2022-08-29 19:00:10.1802823 -07:00
3	2022-11-23 19:03:17.1341212 -08:00
4	2022-12-07 19:02:44.9223430 -08:00
5	2022-11-23 19:04:42.4532233 -08:00
6	2022-12-07 19:05:00.6779341 -08:00
7	2022-12-21 09:17:20.8600203 -08:00
8	2022-12-29 13:51:34.1732389 -08:00
9	2022-12-29 13:09:23.3498327 -08:00
10	2022-12-21 09:31:17.5302483 -08:00
11	2022-12-21 09:34:37.4441480 -08:00
12	2022-11-08 09:25:24.8650237 -08:00
13	2022-09-29 14:25:18.5195997 -07:00
14	2022-12-21 09:36:04.5257032 -08:00
1d

Cell Formulas
Range	Formula
B1	B1	=MAX(LEFT(A2:A14,10)+MID(A2:A14,12,8))

Format to your preference

 

[kyddrivers]

Thank you, Dave and Micron, for your help with this problem!

 

[Micron]

So you have a solution, or no?
You're welcome, BTW.