# Return the latest date



## kyddrivers (Monday at 2:59 PM)

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!

Book1B1RunDateTime22022-08-29 19:00:10.1802823 -07:0032022-11-23 19:03:17.1341212 -08:0042022-12-07 19:02:44.9223430 -08:0052022-11-23 19:04:42.4532233 -08:0062022-12-07 19:05:00.6779341 -08:0072022-12-21 09:17:20.8600203 -08:0082022-12-29 13:51:34.1732389 -08:0092022-12-29 13:09:23.3498327 -08:00102022-12-21 09:31:17.5302483 -08:00112022-12-21 09:34:37.4441480 -08:00122022-11-08 09:25:24.8650237 -08:00132022-09-29 14:25:18.5195997 -07:00142022-12-21 09:36:04.5257032 -08:00Sheet1


----------



## Micron (Monday at 3:13 PM)

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

Make sure formula cell is formatted as date.


----------



## kyddrivers (Monday at 3:37 PM)

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 (Monday at 3:57 PM)

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 (Monday at 4:40 PM)

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.  

Book1BCD1RunDateTime2022-12-29 13:512022-12-29 13:51:34.1732389 -08:0022022-08-29 19:00:10.1802823 -07:008/29/2022 19:00N32022-11-23 19:03:17.1341212 -08:0011/23/2022 19:03N42022-12-07 19:02:44.9223430 -08:0012/7/2022 19:02N52022-11-23 19:04:42.4532233 -08:0011/23/2022 19:04N62022-12-07 19:05:00.6779341 -08:0012/7/2022 19:05N72022-12-21 09:17:20.8600203 -08:0012/21/2022 9:17N82022-12-29 13:51:34.1732389 -08:0012/29/2022 13:51Y92022-12-29 13:09:23.3498327 -08:0012/29/2022 13:09N102022-12-21 09:31:17.5302483 -08:0012/21/2022 9:31N112022-12-21 09:34:37.4441480 -08:0012/21/2022 9:34N122022-11-08 09:25:24.8650237 -08:0011/8/2022 9:25N132022-09-29 14:25:18.5195997 -07:009/29/2022 14:25N142022-12-21 09:36:04.5257032 -08:0012/21/2022 9:36N152022-12-29 13:51:34.1732389 -08:0012/29/2022 13:51YSheet1Cell FormulasRangeFormulaC1C1=TEXT(MAX(C2:C15), "yyyy-mm-dd h:mm")D1D1=XLOOKUP("Y",D2:D15,B2:B15)D2:D15D2=IF($C$1=LEFT(B2,16), "Y","N")


----------



## Micron (Monday at 5:13 PM)

kyddrivers said:


> 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​RunDateTime2​2022-08-29 19:00:10.1802823 -07:0008/29/2022 19:00:10​12/29/2022 13:51:34​=MAX(C8:C9)3​2022-11-23 19:03:17.1341212 -08:0011/23/2022 19:03:17​ seems right4​2022-12-07 19:02:44.9223430 -08:0012/07/2022 19:02:45​5​2022-11-23 19:04:42.4532233 -08:0011/23/2022 19:04:42​6​2022-12-07 19:05:00.6779341 -08:0012/07/2022 19:05:01​7​2022-12-21 09:17:20.8600203 -08:0012/21/2022 09:17:21​8​2022-12-29 13:51:34.1732389 -08:0012/29/2022 13:51:34​9​2022-12-29 13:09:23.3498327 -08:0012/29/2022 13:09:23​10​2022-12-21 09:31:17.5302483 -08:0012/21/2022 09:31:18​11​2022-12-21 09:34:37.4441480 -08:0012/21/2022 09:34:37​12​2022-11-08 09:25:24.8650237 -08:0011/08/2022 09:25:25​13​2022-09-29 14:25:18.5195997 -07:0009/29/2022 14:25:19​14​2022-12-21 09:36:04.5257032 -08:0012/21/2022 09:36:05​


----------



## Dave Patton (Monday at 5:27 PM)

Maxif.xlsmAB1RunDateTime29-Dec-22 13:5122022-08-29 19:00:10.1802823 -07:0032022-11-23 19:03:17.1341212 -08:0042022-12-07 19:02:44.9223430 -08:0052022-11-23 19:04:42.4532233 -08:0062022-12-07 19:05:00.6779341 -08:0072022-12-21 09:17:20.8600203 -08:0082022-12-29 13:51:34.1732389 -08:0092022-12-29 13:09:23.3498327 -08:00102022-12-21 09:31:17.5302483 -08:00112022-12-21 09:34:37.4441480 -08:00122022-11-08 09:25:24.8650237 -08:00132022-09-29 14:25:18.5195997 -07:00142022-12-21 09:36:04.5257032 -08:001dCell FormulasRangeFormulaB1B1=MAX(LEFT(A2:A14,10)+MID(A2:A14,12,8))

Format to your preference


----------



## kyddrivers (Tuesday at 4:23 PM)

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


----------



## Micron (Tuesday at 4:49 PM)

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


----------

