0

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?

Improve this question

1 Answer 1

Reset to default
2

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

Improve this answer
2
  • OK but how to do that?
    – Ricibob
    Apr 20, 2014 at 0:12
  • See my EDIT#1 ........................
    – Gary's Student
    Apr 20, 2014 at 0:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .