Convert a Text Format cell to a Date Format Cell

[binetht]

Hi All,

I have 3 separate dates+times as below.

16/12/2020 15:13:41

21/12/2020 17:28:50

21/12/2020 17:28:55

From these 3 separate cells, I want to find the Latest and Earliest Times.

But when I checked the cell's type, it is 2. I think that means this is a Text type of cell. not a date type cell.

What should I do to find the latest and earliest?

 

[Peter_SSs]

Format the formula cells with dd/mm/yyyy hh:mm:ss

20 12 30.xlsm
	A	B	C	D	E
1				Min	Max
2	16/12/2020 15:13:41	21/12/2020 17:28:50	21/12/2020 17:28:55	16/12/2020 15:13:41	21/12/2020 17:28:55
Date Time

Cell Formulas
Range	Formula
D2	D2	=MIN(--A2:C2)
E2	E2	=MAX(--A2:C2)

 

[maabadi]

Hi Peter. I want to Test your formula, But I don't know why it Show error to me.

Book1
	A	B	C	D	E
1				Min	Max
2	12/30/2020 7:30	12/30/2020 7:39	12/30/2020 7:43	#VALUE!	#VALUE!
3
4	16/12/2020 15:13:41	21/12/2020 17:28:50	21/12/2020 17:28:55	#VALUE!	#VALUE!
5
6	12/30/2020 0:00	12/30/2020 8:50	12/30/2020 8:51	#VALUE!	#VALUE!
Sheet1

Cell Formulas
Range	Formula
D2,D6,D4	D2	=MIN(--A2:C2)
E2,E6,E4	E2	=MAX(--A2:C2)

 

[Peter_SSs]

Looks like your columns A:C are not text as the original question, though that shouldn't be an issue.
Your dates are also in a m/d/y format whereas the OPs are in d/m/y format. Again I don't really think that should be an issue, but try with the dates in d/m/y format and see what happens.

What is the d/m/y setting in your Windows Regional settings?

 

[maabadi]

My System Regional setting is English(United States) also I change custom format to mm/dd/yyyy h:mm;@ ,But it doesn't work.
For d/m/y also not working.

 

[Peter_SSs]

Then I am not sure why it is not working for you.

 

[binetht]

Then I am not sure why it is not working for you.

Hi, I changed the regional formatting like this and now its working.

 

[Peter_SSs]

Hi, I changed the regional formatting like this and now its working.

Yes, that short date format (d/m/y) is what I meant. It matches the data in your sheet. Glad you got it working.

 

[binetht]

and there is another issue,

Raw SSD 12th 12PM to 19th 12 PM.xlsx
	O	P			S	T	U	V	W	X	Y	Z	AA	AB
1	Planned Sewing End date	Actual Sewing End date			Plan Finish date Node 1	Plan Finish date Node 2	Plan Finish date Node 3	Plan Finish date Node 4	Actual Finish date Node 1	Actual Finish date Node 2	Actual Finish date Node 3	Actual Finish date Node 4	Earliest Plan Date	Latest End Date
2	06/10/2020 10:00:0008/10/2020 13:36:0012/10/2020 09:18:0012/10/2020 14:42:00	14/12/2020 12:29:0607/10/2020 15:55:1609/10/2020 16:25:3012/10/2020 11:05:12			6-Oct-2020 10:00 AM	8-Oct-2020 01:36 PM	12-Oct-2020 09:18 AM	12-Oct-2020 02:42 PM	14-Dec-2020 12:29 PM	7-Oct-2020 03:55 PM	9-Oct-2020 04:25 PM	12-Oct-2020 11:05 AM	6-Oct-2020 10:00 AM	7-Oct-2020 03:55 PM
3	11-03-20 15:30	16/12/2020 15:32:51			11-Mar-2020 03:30 PM	#VALUE!	#VALUE!	#VALUE!	16-Dec-2020 03:32 PM	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
Sheet1

Cell Formulas
Range	Formula
AA2:AA3	AA2	=MIN(--S2#)
AB2:AB3	AB2	=MIN(--W2#)
S2:V3	S2	=--MID(O2,SEQUENCE(,4,,19),19)
W2:Z3	W2	=--MID(P2,SEQUENCE(,4,,19),19)
Dynamic array formulas.

As you can see above, If the cell contains 4 Date and times(Like Cell O2 and P2), then it will split and give the date correctly,

but if there is 3 or 2 or 1 value(Like Cell O3 and P3), then there will be a " value" Error.

S2,T2,U2,V2 are the split values of O2 Cell and W2,X2,Y2,Z2 are the split values of P2 Cell.

(AA2 is the earliest value of O2 Cell and AB2 is the largest value of P2 Cell)

due to that "Value" Error, Can't find the earliest or latest dates. What should I Do?

 

[Peter_SSs]

As you can see above, If the cell contains 4 Date and times(Like Cell O2 and P2), then it will split and give the date correctly,

but if there is 3 or 2 or 1 value(Like Cell O3 and P3), then there will be a " value" Error.

That is because in the other thread where you asked about this you only gave one example and when helpers assumed all cells had 4 date/time values you never mentioned any issues about different numbers of values.
Remember, you are very familiar with your data and any variations but we only know what you show and tell us.

Assuming that the data in column A is formatted as Text then try this to get the split values & get MIN/MAX

binetht.xlsm
	A	B	C	D	E	F	G
1						Min	Max
2	06-10-2020 10:00:0008-10-2020 13:36:0012-10-2020 09:18:0012-10-2020 14:42:00	06/10/2020 10:00:00	08/10/2020 13:36:00	12/10/2020 09:18:00	12/10/2020 14:42:00	06/10/2020 10:00:00	12/10/2020 14:42:00
3	06-10-2020 10:00:00	06/10/2020 10:00:00				06/10/2020 10:00:00	06/10/2020 10:00:00
4	01-11-2020 08:15:0008-10-2020 13:01:0012-03-2020 09:18:00	01/11/2020 08:15:00	08/10/2020 13:01:00	12/03/2020 09:18:00		12/03/2020 09:18:00	01/11/2020 08:15:00
Split Date & Time (4)

Cell Formulas
Range	Formula
F2:F4	F2	=MIN(B2#)
G2:G4	G2	=MAX(B2#)
B2:E2,B4:D4,B3	B2	=--MID(A2,SEQUENCE(,LEN(A2)/19,,19),19)
Dynamic array formulas.