MIN and MAX with Dates returns 00/01/1900

[Motox]

Hi,

I have a column of dates and want to use Min and Max to return the earliest and latest dates but all i get is 00/01/1900 for both.

What do i need to change?

Thanks very much

Dave

[Nisha Dhawan]

Hi Dave,

Please find the attached excel file, as per my understanding I gave the solution please check is it useful for you otherwise share your excel file with example so that we will be able to understand easily and can provide you appropriate answer.

Thanks
Nisha

[benishiryo]

hi dave. what i am guessing is that your dates you are seeing are not real dates. Excel recognises dates as integers & time as decimals. to verify, type in 1jan2013 in A1. and 12 pm in B1. Format both cells to General. you will see that A1 is 41275 (meaning 41,275th day from 1 Jan 1900) & B1 is 0.5 (1/2 a day).*
So if you are seeing 0/1/1900, it MIGHT be because those are text and excel see it as 0

could you select the range & press CTRL + SHIFT + ~
you should see all of them become numbers if they are real dates. Undo it to revert to normal.

if they are not, select the range again. assuming your date is in the format of DMY. go to Data -> Text to Column -> Delimited -> Next -> Next -> Date: DMY -> Finish. the red part is dependent on what you are seeing the date as.

like Nisha suggested, upload a file if necessary

[Motox]

Thanks very much guys, I was able to get it working using:

if they are not, select the range again. assuming your date is in the format of DMY. go to Data -> Text to Column -> Delimited -> Next -> Next -> Date: DMY -> Finish. the red part is dependent on what you are seeing the date as.

Dave