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?

Hi,

Am Mon, 10 Oct 2016 22:24:05 -0700 (PDT) schrieb GARYWC:

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?

MIN date: =MIN(DATEVALUE(SUBSTITUTE(LEFT(A1:B1,10),":","/")))
MIN time: =MIN(1*RIGHT(A1:B1,8))
MIN timestamp: =MIN(DATEVALUE(SUBSTITUTE(LEFT(A1:B1,10),":","/"))+1*RIGHT(A1:B1,8))

All formulas are array formulas to enter with CTRL+Shift+Enter


Regards
Claus B.
--
Windows10
Office 2016

The cells containing the text-formatted strings are C1 and D1.

C1 contains: 2016:08:23 18:43:05
D1 contains: 2016:08:21 17:39:36

so I changed your formulas to:

=MIN(DATEVALUE(SUBSTITUTE(LEFT(C1:D1,10),":","/")))
=MIN(1*RIGHT(C1:D1,8))
=MIN(DATEVALUE(SUBSTITUTE(LEFT(C1:D1,10),":","/"))+1*RIGHT(C1:D1,8))

When I paste those new formulas into my spreadsheet, the three results are #VALUE!

Why don't I get usable results?

Hi,

Am Thu, 13 Oct 2016 08:25:23 -0700 (PDT) schrieb GARYWC:

The cells containing the text-formatted strings are C1 and D1.

C1 contains: 2016:08:23 18:43:05
D1 contains: 2016:08:21 17:39:36

so I changed your formulas to:

=MIN(DATEVALUE(SUBSTITUTE(LEFT(C1:D1,10),":","/")))
=MIN(1*RIGHT(C1:D1,8))
=MIN(DATEVALUE(SUBSTITUTE(LEFT(C1:D1,10),":","/"))+1*RIGHT(C1:D1,8))

When I paste those new formulas into my spreadsheet, the three results are #VALUE!

Why don't I get usable results?

did you insert the formulas with CTRL+Shift+Enter?


Regards
Claus B.
--
Windows10
Office 2016

Now the results a

42603 0.735833333 42603.73583

Hi,

Am Thu, 13 Oct 2016 09:01:01 -0700 (PDT) schrieb GARYWC:

Now the results a

42603 0.735833333 42603.73583

format the first result MM.dd.yyyy
the second with hh:mm:ss
and the third one with
MM.dd.yyyy hh:mm:ss


Regards
Claus B.
--
Windows10
Office 2016

Do these results look correct?

C D E F G
2009:09:30 19:45:18 2009:06:09 17:57:55 39973 0.748553241 39973.74855
2009:09:30 19:45:20 2009:06:09 17:58:21 39973 0.748854167 39973.74885
2009:09:30 19:45:44 2009:08:21 12:22:46 40046 0.515810185 40046.51581
2009:09:30 19:45:46 2009:08:21 12:23:05 40046 0.516030093 40046.51603
2016:08:08 12:31:50 2016:08:08 12:31:50 42590 0.522106481 42590.52211
2016:08:23 18:43:05 2016:08:21 17:39:36 42603 0.735833333 42603.73583

Do these results look correct?

2009:09:30 19:45:18 | 2009:06:09 17:57:55 |Â*39973 | 0.748553241 | 39973.74855
2009:09:30 19:45:20 | 2009:06:09 17:58:21 |Â*39973Â*|Â*0.748854167 | 39973.74885
2009:09:30 19:45:44 | 2009:08:21 12:22:46 |Â*40046Â*| 0.515810185 |Â*40046.51581 2009:09:30 19:45:46 | 2009:08:21 12:23:05 |Â*40046Â*| 0.516030093 | 40046.51603
2016:08:08 12:31:50 | 2016:08:08 12:31:50 |Â*42590 | 0.522106481 | 42590.52211
2016:08:23 18:43:05 | 2016:08:21 17:39:36 |Â*42603 | 0.735833333 | 42603.73583

Do these results look correct?

2009:09:30 19:45:18 | 2009:06:09 17:57:55 |Â*39973 | 0.748553241 | 39973.74855
2009:09:30 19:45:20 | 2009:06:09 17:58:21 |Â*39973Â*|Â*0.748854167 | 39973.74885
2009:09:30 19:45:44 | 2009:08:21 12:22:46 |Â*40046Â*| 0.515810185 |Â*40046.51581
2009:09:30 19:45:46 | 2009:08:21 12:23:05 |Â*40046Â*| 0.516030093 | 40046.51603
2016:08:08 12:31:50 | 2016:08:08 12:31:50 |Â*42590 | 0.522106481 | 42590.52211
2016:08:23 18:43:05 | 2016:08:21 17:39:36 |Â*42603 | 0.735833333 | 42603.73583

Hi,

Am Thu, 13 Oct 2016 15:25:05 -0700 (PDT) schrieb GARYWC:

2009:09:30 19:45:18 2009:06:09 17:57:55 39973 0.748553241 39973.74855
2009:09:30 19:45:20 2009:06:09 17:58:21 39973 0.748854167 39973.74885
2009:09:30 19:45:44 2009:08:21 12:22:46 40046 0.515810185 40046.51581
2009:09:30 19:45:46 2009:08:21 12:23:05 40046 0.516030093 40046.51603
2016:08:08 12:31:50 2016:08:08 12:31:50 42590 0.522106481 42590.52211
2016:08:23 18:43:05 2016:08:21 17:39:36 42603 0.735833333 42603.73583

look he
https://1drv.ms/x/s!AKMiGBK2qniT7jI


Regards
Claus B.
--
Windows10
Office 2016

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