Time function needed
 

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.

Time function needed
 

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

--
Jacob


"wynand" wrote:

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.

Time function needed
 

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.


"wynand" wrote:

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.

Time function needed
 

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

"wynand" wrote in message
...
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.

Time function needed
 

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

"Mike H" wrote:

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.


"wynand" wrote:

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.

Time function needed
 

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

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

"Mike H" wrote:

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.


"wynand" wrote:

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.

Time function needed
 

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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

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

"Mike H" wrote:

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.


"wynand" wrote:

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.

Time function needed
 

THANK YOU THAT HELPED

"David Biddulph" wrote:

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

"wynand" wrote in message
...
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.


.