View Single Post
  #11   Report Post  
Posted to microsoft.public.excel.programming
Bruno Campanini[_2_] Bruno Campanini[_2_] is offline
external usenet poster
 
Posts: 74
Default Compare cell contents

GARYWC formulated the question :
A1 and B1 have a text format.

A1 contains 2016:08:11 23:50:55
B1 contains 2016:08:07 14:47:31

What formula will determine which cell contains the earliest date/time?


Having:
A1 2016:08:11 23:50:55
A2 2016:08:07 14:47:31
A3 2016:08:11 23:50:56

Drag:
=DATEVALUE(SUBSTITUTE(MID(A1,1,10),":","-") &
RIGHT(A1,9))+TIMEVALUE(SUBSTITUTE(MID(A1,1,10),":" ,"-") & RIGHT(A1,9))
from B1 to B3

Then:
Max(B1:B3) = 42593.9937
Format this cell as yyyy-mm-dd hh:mm:ss
and you'll get 2016-08-11 23:50:56

Bruno