Groups > microsoft.public.excel.programming > #109453 > unrolled thread

Earliest date & time

Started by	GARYWC <gcotterl@earthlink.net>
First post	2016-10-23 21:34 -0700
Last post	2016-10-25 13:24 +0200
Articles	19 — 6 participants

Back to article view | Back to microsoft.public.excel.programming

  Earliest date & time GARYWC <gcotterl@earthlink.net> - 2016-10-23 21:34 -0700
    Re: Earliest date & time "Auric__" <not.my.real@email.address> - 2016-10-24 14:23 +0000
      Re: Earliest date & time GARYWC <gcotterl@earthlink.net> - 2016-10-24 08:28 -0700
      Re: Earliest date & time GARYWC <gcotterl@earthlink.net> - 2016-10-24 11:12 -0700
        Re: Earliest date & time isabelle <i@v.invalid> - 2016-10-24 16:27 -0400
          Re: Earliest date & time isabelle <i@v.invalid> - 2016-10-24 16:41 -0400
      Re: Earliest date & time GARYWC <gcotterl@earthlink.net> - 2016-10-26 12:15 -0700
        Re: Earliest date & time isabelle <i@v.invalid> - 2016-10-26 18:47 -0400
      Re: Earliest date & time GARYWC <gcotterl@earthlink.net> - 2016-10-26 15:52 -0700
        Re: Earliest date & time isabelle <i@v.invalid> - 2016-10-26 19:34 -0400
          Re: Earliest date & time isabelle <i@v.invalid> - 2016-10-26 19:37 -0400
        Re: Earliest date & time isabelle <i@v.invalid> - 2016-10-26 19:53 -0400
      Re: Earliest date & time GARYWC <gcotterl@earthlink.net> - 2016-10-26 16:54 -0700
        Re: Earliest date & time isabelle <i@v.invalid> - 2016-10-26 20:04 -0400
      Re: Earliest date & time GARYWC <gcotterl@earthlink.net> - 2016-10-26 20:25 -0700
        Re: Earliest date & time isabelle <i@v.invalid> - 2016-10-27 01:12 -0400
    Re: Earliest date & time Bruno Campanini <brunocam@libero.it> - 2016-10-25 03:16 +0200
    Re: Earliest date & time dienmaytrangiaabc <dienmaytrangiaabc@gmail.com> - 2016-10-25 05:15 +0100
    Re: Earliest date & time Claus Busch <claus_busch@t-online.de> - 2016-10-25 13:24 +0200

#109453 — Earliest date & time

From	GARYWC <gcotterl@earthlink.net>
Date	2016-10-23 21:34 -0700
Subject	Earliest date & time
Message-ID	<66bdd866-f844-489f-b30c-daf1794ac287@googlegroups.com>

Cols C, D and E have cells that contain either a date & time or spaces  

--------- C ------- --------- D ------- --------- E -------                 
---date--- --time-- ---date--- --time-- ---date--- --time--

2016:10:21 22:00:01 2016:10:17 10:57:40 2016:10:17 10:57:40
2016:10:15 07:07:02 2016:10:15 14:07:02 2016:10:15 14:07:02
2016:10:16 02:09:02 2016:10:16 09:09:02 2016:10:16 09:09:02
2016:10:15 07:52:04 2016:10:15 14:52:04 2016:10:15 14:52:04
2016:10:16 02:30:07 2016:10:16 09:30:07 2016:10:16 09:30:07
2008:10:21 10:21:08  
2016:10:15 07:53:10 2016:10:15 14:53:10 2016:10:15 14:53:10
2016:10:15 07:06:12 2016:10:15 14:06:12 2016:10:15 14:06:12
2008:10:21 10:21:14  
2016:10:15 07:19:16 2016:10:15 14:19:16 2016:10:15 14:19:16
2016:10:15 07:53:22 2016:10:15 14:53:22 2016:10:15 14:53:22
2016:10:15 07:02:28 2016:10:15 14:02:28 2016:10:15 14:02:28
2016:10:23 09:23:40 2016:10:13 20:19:36 
2016:10:21 21:59:40 2016:10:17 10:57:51 2016:10:17 10:57:51
2005:12:08 09:22:42  
2012:04:09 17:18:46 2011:07:18 14:40:24 
2016:10:16 02:07:53 2016:10:16 09:07:53 2016:10:16 09:07:53
2016:10:17 03:59:55 2016:10:17 10:59:55 2016:10:17 10:59:55

What formula do I use to find the earliest date & time in each row?

[toc] | [next] | [standalone]

#109459

From	"Auric__" <not.my.real@email.address>
Date	2016-10-24 14:23 +0000
Message-ID	<XnsA6AB4B318505Dauricauricauricauric@213.239.209.88>
In reply to	#109453

GARYWC wrote (repeatedly):

> Cols C, D and E have cells that contain either a date & time or spaces

[snip]

> What formula do I use to find the earliest date & time in each row?

If Excel recognizes the format entered as a date, the MIN worksheet function 
will do it:

  =MIN(C1:E1)

Otherwise, you can just compare the text. Here's some code showing one way to 
do it:

  Function textMin(what As Range) As String
    Dim tmp, r As Range
    tmp = "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ"
    For Each r In what
      If r.Value < tmp Then tmp = r.Value
    Next
    textMin = tmp
  End Function

Called like MIN():

  =textMin(C1:E1)

-- 
- You think it's funny?
- Not really.

[toc] | [prev] | [next] | [standalone]

#109461

From	GARYWC <gcotterl@earthlink.net>
Date	2016-10-24 08:28 -0700
Message-ID	<dc1560b5-9749-4fc9-b910-2c08b497b1c9@googlegroups.com>
In reply to	#109459

I posted my topic repeatedly because I couldn't edit the previous posts when I wanted to clarify my question (or format my example better).

[toc] | [prev] | [next] | [standalone]

#109462

From	GARYWC <gcotterl@earthlink.net>
Date	2016-10-24 11:12 -0700
Message-ID	<69f9666f-ab7f-4044-9fee-5a9adddc95d1@googlegroups.com>
In reply to	#109459

 =MIN(C1:E1) returns 0 (zero) for all rows.

[toc] | [prev] | [next] | [standalone]

#109463

From	isabelle <i@v.invalid>
Date	2016-10-24 16:27 -0400
Message-ID	<nulqrl$1ksk$1@gioia.aioe.org>
In reply to	#109462

Hi,

to transform a (text)date  in real date
=DATE(LEFT(C2,4),MID(C2,6,2),MID(C2,9,2))+RIGHT(C2,8)

isabelle


Le 2016-10-24 à 14:12, GARYWC a écrit :
>  =MIN(C1:E1) returns 0 (zero) for all rows.
>

[toc] | [prev] | [next] | [standalone]

#109464

From	isabelle <i@v.invalid>
Date	2016-10-24 16:41 -0400
Message-ID	<nulrli$1m75$1@gioia.aioe.org>
In reply to	#109463

for versions xl2007+
you can use:
=IFERROR(DATE(LEFT(C2,4),MID(C2,6,2),MID(C2,9,2))+RIGHT(C2,8),"")

isabelle

Le 2016-10-24 à 16:27, isabelle a écrit :
> Hi,
>
> to transform a (text)date  in real date
> =DATE(LEFT(C2,4),MID(C2,6,2),MID(C2,9,2))+RIGHT(C2,8)
>
> isabelle
>
>
> Le 2016-10-24 à 14:12, GARYWC a écrit :
>>  =MIN(C1:E1) returns 0 (zero) for all rows.
>>

[toc] | [prev] | [next] | [standalone]

#109476

From	GARYWC <gcotterl@earthlink.net>
Date	2016-10-26 12:15 -0700
Message-ID	<34cd9877-d0f2-4ae2-bf6d-cb9d41f8bcf1@googlegroups.com>
In reply to	#109459

The dates and times are now in separate columns (a, b, c and d).
I also changed the format of the dates from ccyy:mm:dd to dd/mm/ccyy:

Now, what formula(s) do I use to find the earliest date & time in each row?

			
01/18/2014	09:53:04	02/17/2014	07:18:05
10/18/2012	16:20:42	11/12/2012	15:43:31
10/18/2012	16:21:44	11/12/2012	14:22:09
10/18/2012	16:21:42	11/12/2012	12:56:36
10/18/2012	16:21:28	11/12/2012	10:15:28
10/18/2012	16:21:22	11/12/2012	10:03:44
10/18/2012	16:21:12	11/12/2012	09:57:59
10/18/2012	16:19:08	11/12/2012	09:38:17
10/18/2012	16:18:44	11/12/2012	09:35:19
10/18/2012	16:21:18	11/11/2012	13:09:25
10/18/2012	16:21:14	11/11/2012	13:06:47
10/18/2012	16:20:38	11/11/2012	10:34:58
10/18/2012	16:20:30	11/11/2012	09:44:44
10/18/2012	16:20:30	11/11/2012	09:40:23
10/18/2012	16:20:12	11/11/2012	09:24:10
10/18/2012	16:19:12	11/11/2012	09:00:45
10/18/2012	16:17:56	11/11/2012	08:46:37
10/18/2012	16:17:30	11/11/2012	08:43:19
10/18/2012	16:17:24	11/11/2012	08:42:59

[toc] | [prev] | [next] | [standalone]

#109478

From	isabelle <i@v.invalid>
Date	2016-10-26 18:47 -0400
Message-ID	<nurbpo$12ae$1@gioia.aioe.org>
In reply to	#109476

Hi,

if there are no blank cells
=MIN(A1+B1,C1+D1)

if not
=IF(AND(A1+B1<>0,C1+D1<>0),MIN(A1+B1,C1+D1),SUM(A1:D1))

isabelle

Le 2016-10-26 à 15:15, GARYWC a écrit :
> The dates and times are now in separate columns (a, b, c and d).
> I also changed the format of the dates from ccyy:mm:dd to dd/mm/ccyy:
>
> Now, what formula(s) do I use to find the earliest date & time in each row?
>
> 			
> 01/18/2014	09:53:04	02/17/2014	07:18:05
> 10/18/2012	16:20:42	11/12/2012	15:43:31
> 10/18/2012	16:21:44	11/12/2012	14:22:09
> 10/18/2012	16:21:42	11/12/2012	12:56:36
> 10/18/2012	16:21:28	11/12/2012	10:15:28
> 10/18/2012	16:21:22	11/12/2012	10:03:44
> 10/18/2012	16:21:12	11/12/2012	09:57:59
> 10/18/2012	16:19:08	11/12/2012	09:38:17
> 10/18/2012	16:18:44	11/12/2012	09:35:19
> 10/18/2012	16:21:18	11/11/2012	13:09:25
> 10/18/2012	16:21:14	11/11/2012	13:06:47
> 10/18/2012	16:20:38	11/11/2012	10:34:58
> 10/18/2012	16:20:30	11/11/2012	09:44:44
> 10/18/2012	16:20:30	11/11/2012	09:40:23
> 10/18/2012	16:20:12	11/11/2012	09:24:10
> 10/18/2012	16:19:12	11/11/2012	09:00:45
> 10/18/2012	16:17:56	11/11/2012	08:46:37
> 10/18/2012	16:17:30	11/11/2012	08:43:19
> 10/18/2012	16:17:24	11/11/2012	08:42:59
>

[toc] | [prev] | [next] | [standalone]

#109479

From	GARYWC <gcotterl@earthlink.net>
Date	2016-10-26 15:52 -0700
Message-ID	<334ca7bc-9f6e-4220-aa36-87833984185d@googlegroups.com>
In reply to	#109459

Great!

Is there any way to know which cells (A1 and B1) or (C1 and D1) resulted in the serial number?

[toc] | [prev] | [next] | [standalone]

#109480

From	isabelle <i@v.invalid>
Date	2016-10-26 19:34 -0400
Message-ID	<nurehn$15mo$1@gioia.aioe.org>
In reply to	#109479

it would have been better to keep the date and time set in a cell

isabelle


Le 2016-10-26 à 18:52, GARYWC a écrit :
> Great!
>
> Is there any way to know which cells (A1 and B1) or (C1 and D1) resulted in the serial number?
>

[toc] | [prev] | [next] | [standalone]

#109481

From	isabelle <i@v.invalid>
Date	2016-10-26 19:37 -0400
Message-ID	<nuren8$15mo$2@gioia.aioe.org>
In reply to	#109480

you can also use a pivot table
isabelle

Le 2016-10-26 à 19:34, isabelle a écrit :
> it would have been better to keep the date and time set in a cell
>
> isabelle
>
>
> Le 2016-10-26 à 18:52, GARYWC a écrit :
>> Great!
>>
>> Is there any way to know which cells (A1 and B1) or (C1 and D1) resulted in
>> the serial number?
>>

[toc] | [prev] | [next] | [standalone]

#109482

From	isabelle <i@v.invalid>
Date	2016-10-26 19:53 -0400
Message-ID	<nurfl4$16ue$1@gioia.aioe.org>
In reply to	#109479

if Min formula is in column F

=IF(F1=A1+B1,ADDRESS(ROW(),1),IF(F1=C1+D1,ADDRESS(ROW(),3),""))

isabelle

Le 2016-10-26 à 18:52, GARYWC a écrit :
> Great!
>
> Is there any way to know which cells (A1 and B1) or (C1 and D1) resulted in the serial number?
>

[toc] | [prev] | [next] | [standalone]

#109483

From	GARYWC <gcotterl@earthlink.net>
Date	2016-10-26 16:54 -0700
Message-ID	<f74c855e-7324-4004-b15e-4015c24ca8a9@googlegroups.com>
In reply to	#109459

The dates and times are still in A1/B1 and in C1/D1 and the serial number is in E1.

Which date and time does the serial number represent?

[toc] | [prev] | [next] | [standalone]

#109484

From	isabelle <i@v.invalid>
Date	2016-10-26 20:04 -0400
Message-ID	<nurga7$17ms$1@gioia.aioe.org>
In reply to	#109483

=IF(E1=A1+B1,ADDRESS(ROW(),1,4)&":"&ADDRESS(ROW(),2,4),IF(E1=C1+D1,ADDRESS(ROW(),3,4)&":"&ADDRESS(ROW(),4,4),""))

isabelle

Le 2016-10-26 à 19:54, GARYWC a écrit :
> The dates and times are still in A1/B1 and in C1/D1 and the serial number is in E1.
>
> Which date and time does the serial number represent?
>

[toc] | [prev] | [next] | [standalone]

#109485

From	GARYWC <gcotterl@earthlink.net>
Date	2016-10-26 20:25 -0700
Message-ID	<cbc4b620-1e18-458b-9a95-37eb44e151f5@googlegroups.com>
In reply to	#109459

Thank you (Je vous remercie), isabelle

[toc] | [prev] | [next] | [standalone]

#109487

From	isabelle <i@v.invalid>
Date	2016-10-27 01:12 -0400
Message-ID	<nus2c3$1r9a$1@gioia.aioe.org>
In reply to	#109485

you are welcome. i'm glad i could help

Le 2016-10-26 à 23:25, GARYWC a écrit :
> Thank you (Je vous remercie), isabelle
>

[toc] | [prev] | [next] | [standalone]

#109466

From	Bruno Campanini <brunocam@libero.it>
Date	2016-10-25 03:16 +0200
Message-ID	<numbov$c6d$1@gioia.aioe.org>
In reply to	#109453

GARYWC wrote :
> Cols C, D and E have cells that contain either a date & time or spaces  
>
> --------- C ------- --------- D ------- --------- E -------                 
> ---date--- --time-- ---date--- --time-- ---date--- --time--
>
> 2016:10:21 22:00:01 2016:10:17 10:57:40 2016:10:17 10:57:40
> 2016:10:15 07:07:02 2016:10:15 14:07:02 2016:10:15 14:07:02
> 2016:10:16 02:09:02 2016:10:16 09:09:02 2016:10:16 09:09:02
> 2016:10:15 07:52:04 2016:10:15 14:52:04 2016:10:15 14:52:04
> 2016:10:16 02:30:07 2016:10:16 09:30:07 2016:10:16 09:30:07
> 2008:10:21 10:21:08  
> 2016:10:15 07:53:10 2016:10:15 14:53:10 2016:10:15 14:53:10
> 2016:10:15 07:06:12 2016:10:15 14:06:12 2016:10:15 14:06:12
> 2008:10:21 10:21:14  
> 2016:10:15 07:19:16 2016:10:15 14:19:16 2016:10:15 14:19:16
> 2016:10:15 07:53:22 2016:10:15 14:53:22 2016:10:15 14:53:22
> 2016:10:15 07:02:28 2016:10:15 14:02:28 2016:10:15 14:02:28
> 2016:10:23 09:23:40 2016:10:13 20:19:36 
> 2016:10:21 21:59:40 2016:10:17 10:57:51 2016:10:17 10:57:51
> 2005:12:08 09:22:42  
> 2012:04:09 17:18:46 2011:07:18 14:40:24 
> 2016:10:16 02:07:53 2016:10:16 09:07:53 2016:10:16 09:07:53
> 2016:10:17 03:59:55 2016:10:17 10:59:55 2016:10:17 10:59:55
>
> What formula do I use to find the earliest date & time in each row?

You put the same question 10 days ago, for one column
and I gave you the answer.
Now for three columns with one or more blank cells:

=MIN(IF(ISBLANK(A1),"",DATEVALUE(SUBSTITUTE(MID(A1,1,10),":","-") & 
RIGHT(A1,9))+TIMEVALUE(SUBSTITUTE(MID(A1,1,10),":","-") & 
RIGHT(A1,9))),IF(ISBLANK(B1),"",DATEVALUE(SUBSTITUTE(MID(B1,1,10),":","-") 
& RIGHT(B1,9))+TIMEVALUE(SUBSTITUTE(MID(B1,1,10),":","-") & 
RIGHT(B1,9))),IF(ISBLANK(C1),"",DATEVALUE(SUBSTITUTE(MID(C1,1,10),":","-") 
& RIGHT(C1,9))+TIMEVALUE(SUBSTITUTE(MID(C1,1,10),":","-") & 
RIGHT(C1,9))))

You have data in columns A-C, starting cell: A1
You can replace MIN with MAX.. at your choice.

Bruno

[toc] | [prev] | [next] | [standalone]

#109470

From	dienmaytrangiaabc <dienmaytrangiaabc@gmail.com>
Date	2016-10-25 05:15 +0100
Message-ID	<dienmaytrangiaabc.125499b8@excelbanter.com>
In reply to	#109453

Úp cho Bác mua nhanh bán đắt




-- 
dienmaytrangiaabc

[toc] | [prev] | [next] | [standalone]

#109473

From	Claus Busch <claus_busch@t-online.de>
Date	2016-10-25 13:24 +0200
Message-ID	<nunfc7$54p$1@dont-email.me>
In reply to	#109453

Hi,

Am Sun, 23 Oct 2016 21:34:12 -0700 (PDT) schrieb GARYWC:

> Cols C, D and E have cells that contain either a date & time or spaces  
> 
> --------- C ------- --------- D ------- --------- E -------                 
> ---date--- --time-- ---date--- --time-- ---date--- --time--
> 
> 2016:10:21 22:00:01 2016:10:17 10:57:40 2016:10:17 10:57:40
> 2016:10:15 07:07:02 2016:10:15 14:07:02 2016:10:15 14:07:02
> 2016:10:16 02:09:02 2016:10:16 09:09:02 2016:10:16 09:09:02
> 2016:10:15 07:52:04 2016:10:15 14:52:04 2016:10:15 14:52:04
> 2016:10:16 02:30:07 2016:10:16 09:30:07 2016:10:16 09:30:07
> 2008:10:21 10:21:08  
> 2016:10:15 07:53:10 2016:10:15 14:53:10 2016:10:15 14:53:10
> 2016:10:15 07:06:12 2016:10:15 14:06:12 2016:10:15 14:06:12
> 2008:10:21 10:21:14  
> 2016:10:15 07:19:16 2016:10:15 14:19:16 2016:10:15 14:19:16
> 2016:10:15 07:53:22 2016:10:15 14:53:22 2016:10:15 14:53:22
> 2016:10:15 07:02:28 2016:10:15 14:02:28 2016:10:15 14:02:28
> 2016:10:23 09:23:40 2016:10:13 20:19:36 
> 2016:10:21 21:59:40 2016:10:17 10:57:51 2016:10:17 10:57:51
> 2005:12:08 09:22:42  
> 2012:04:09 17:18:46 2011:07:18 14:40:24 
> 2016:10:16 02:07:53 2016:10:16 09:07:53 2016:10:16 09:07:53
> 2016:10:17 03:59:55 2016:10:17 10:59:55 2016:10:17 10:59:55
> 
> What formula do I use to find the earliest date & time in each row?

all formulas you got 10 days before work as expected. But you have to
format the formula cells with the expected format:

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


Regards
Claus B.
-- 
Windows10
Office 2016

[toc] | [prev] | [standalone]

csiph-web

Earliest date & time

Contents

#109453 — Earliest date & time

#109459

#109461

#109462

#109463

#109464

#109476

#109478

#109479

#109480

#109481

#109482

#109483

#109484

#109485

#109487

#109466

#109470

#109473