Find Minimum Date

[Aviz8945]

Hi,

I have 7 cells with different dates (all in text), How can I find minimum /maximum?

Ex:

4/3/2012 8:00

4/2/2012 17:00

4/4/2012 0:30

04-Apr-12 14:00 A

04-Apr-12 10:00 A

01-Apr-12 20:30 A

04-Apr-12 21:00 A


Thanks in advance,

Avi

 

[Aviz8945]

Hello,

Excel Masters,


Its an urgent...please help me

 

[SirJB7]

Hi, Aviz8945!


All cells have valid date & time values?

If the "A" stands for AM, some of them don't: 1400 A, 2030 A and 21:00 A.


Regards!

 

[Aviz8945]

SirJB7,


I am working on a schedule in Excel, here "A" stands for Actual (completed work).So first 3 lines showng future date(forecast) & last 4 lines showing completed (Actual.

Data is valid!

Regards!

 

[SirJB7]

Hi, Aviz8945!


If you store a date and a letter (or whatsover non-blank additional) in the same cell, well, the cell contains an invalid date/time value. So, data is invalid!


About your question, assuming your data is in A1:A7, try this:

a) in B1 type:

=IZQUIERDA($A1;HALLAR(" ";$A1)-1) -----> in english: =LEFT($A1,SEARCH(" ",$A1)-1)

b) in C1 type:

=EXTRAE($A1&" ";HALLAR(" ";$A1&" ";HALLAR(" ";$A1&" "))+1;HALLAR(" ";$A1&" ";HALLAR(" ";$A1&" ")+1)-HALLAR(" ";$A1&" ")-1) -----> in english: =MID($A1&" ",SEARCH(" ",$A1&" ",SEARCH(" ",$A1&" "))+1,SEARCH(" ",$A1&" ",SEARCH(" ",$A1&" ")+1)-SEARCH(" ",$A1&" ")-1)

c) in D1 type:

=FECHANUMERO(B1) -----> in english: =DATEVALUE(B1)

d) in E1 type:

=HORANUMERO(B1) -----> in english: =TIMEVALUE(B1)

e) in F1 type:

=D1+E1

f) copy B1:F1 to B2:F7

g) in G1 type:

=MAX(F1:F7)

h) in G2 type:

=MIN(F1:F7)


Maximum in G1, minimum in G2.


Regards!

 

[oldchippy]

Hi,


Try this in B1 copied down


=IF(RIGHT(A1,1)="A",LEFT(A1,SEARCH(" ",A1)-1)+MID(A1,11,5),A1+0)


Then in C1


=MAX(B1:B7)


And in C2


=MIN(B1:B7)


Edit: Then custom format C1 & C2 to dd/mm/yyyy hh:mm:ss