Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time formula Needed | Excel Discussion (Misc queries) | |||
Time format help needed | Excel Worksheet Functions | |||
time guru needed | Excel Worksheet Functions | |||
date/time calculation needed | Excel Worksheet Functions | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions |