Time function needed

[wynand]

Is there any function (or vba) to determine the earliest and latest time
(00:00:00) in a range?Tried max, min, small to no avail.

 

[Jacob Skaria]

These functions should work with time..Apply the formula and format the
formula cell to the required time format and see.

Also readout this must read article by Chip Pearson.
http://www.cpearson.com/excel/datetime.htm

 

[Mike H]

Hi,

In Excel times are decimal numbers formatted to look like a recognisable
times. So for example 0.4375 formated as time will display 10:30 or time in
whatever format you choose.

Min and max will therefore work provided your times are properly formatted.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[David Biddulph]

In what way did MAX or MIN not work? What values did you give it, what
result did you get, and what result did you expect?
Do your cells just contain time, or do they contain date & time with just
the time showing?
If you have different dates, are you wanting the earliest combination of
date & time, or the earliest time regardless of date?
If the latter, try
=MIN(MOD(A1:A20,1)) as an array formula (entered with control shift enter),
adjusting your input range to suit your data.

 

[wynand]

Thanks a mil this will work except my time shows the same format no matter
what I change it to (number, etc.)

 

[David Biddulph]

In which case it isn't a time, it's text. You'll need to cure that.
Exactly what format are you seeing for your "times"? If they look like a
genuine Excel time, you may need to check for extraneous spaces, or other
non-printing characters such as non-breaking spaces.

 

[David Biddulph]

I forgot to say, you can check whether text or not with =ISTEXT(your cell
ref) and =ISNUMBER(your cell ref).

 

[wynand]

THANK YOU THAT HELPED

In what way did MAX or MIN not work? What values did you give it, what
result did you get, and what result did you expect?
Do your cells just contain time, or do they contain date & time with just
the time showing?
If you have different dates, are you wanting the earliest combination of
date & time, or the earliest time regardless of date?
If the latter, try
=MIN(MOD(A1:A20,1)) as an array formula (entered with control shift enter),
adjusting your input range to suit your data.
--
David Biddulph




.