Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Compare cell contents

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?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Compare cell contents

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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Compare cell contents

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?
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Compare cell contents

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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Compare cell contents

Now the results a

42603 0.735833333 42603.73583



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Compare cell contents

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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Compare cell contents

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

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Compare cell contents

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
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 80
Default Compare cell contents

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






  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Compare cell contents

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


  #11   Report Post  
Posted to microsoft.public.excel.programming
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare cell contents in adjacent columns ivanoddcat Excel Programming 3 August 20th 07 03:32 PM
COMPARE CELL CONTENTS guy Excel Worksheet Functions 4 December 24th 05 09:29 PM
compare cell contents guy Excel Worksheet Functions 2 December 23rd 05 09:38 PM
Macro to compare cell contents and make calculations Stereolab Excel Programming 3 November 8th 05 10:29 PM
Function syntax to compare cell contents ES Excel Worksheet Functions 2 May 18th 05 03:53 PM


All times are GMT +1. The time now is 05:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"