Excel date time find max / min and max min timespan

Question

I have data time data in excel in the format "04/18/2014 17:09:09". I would like to use MAX and MIN on a range of such dates to get max, min and time span between min-max. I tried using custom format "mm/dd/yyyy hh:mm:ss" but thats hopeless - MAX always returns 01/00/1900 00:00:00. What to do the be able to to time base caclulations on time stamps like this?

Solution

If MAX() is returning 01/00/1900 00:00:00

it means that MAX() is really returning 0

This, in turn, means that your date/times are text rather than numerical values.

You need to convert then to "true" dates before they can be used in calculations.

EDIT#1

With data like yours in column A starting in A1 , in B1 enter:

=DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2))+TIME(MID(A1,12,2),MID(A1,15,2),RIGHT(A1,2))

and copy down

Then you can use you MAX and MIN against column B rather than A

Comments

[Ricibob]

OK but how to do that?