W
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.
(00:00:00) in a range?Tried max, min, small to no avail.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
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
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
M
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.
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.
D
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.
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.
W
wynand
Thanks a mil this will work except my time shows the same format no matter
what I change it to (number, etc.)
what I change it to (number, etc.)
D
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.
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.
D
David Biddulph
I forgot to say, you can check whether text or not with =ISTEXT(your cell
ref) and =ISNUMBER(your cell ref).
ref) and =ISNUMBER(your cell ref).
W
wynand
THANK YOU THAT HELPED
David Biddulph said: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
.