Minimum Date problems

[clumsy-xceller]

Hi I am having trouble with finding an earliest date in a series. Let me explain how I have the sheet set up.

basically its a rental cost calculator,

I have a series of rows in which the user can select whats to be rented, date in and date out. I have 2 columns which check to see that the user has selected a product to rent ie "ISO" or "FLEXI" and not "None" and then transfers the date in and date out from the corresponding collumns. (this may seem a roundabout way of doing this but I have further plans for these extra collumns. If the user has selected "None" then the extra date cells will contain "Not Active" I want to find the minimum of the extra ADatein column and maximum of the ADateout column.

ie

A--------- B---------- C---------- D----------- E----------- F--------
ADatein----ADateout --Product ----Datein ----Dateout

Not Active- Not Active-- None---- 4/6/2010--- 4/6/2010
4/6/2010-- 20/6/2010--- ISO----- 4/6/2010--- 20/6/2010
5/6/2010-- 29/6/2010--- FLEXI---- 5/6/2010-- 29/6/2010


Min Date--- Max Date

I have tried MIN(A3:A5) and get a result of 0, even if all 3 rows have a date in them I still get a 0?? I assume it has something to do with the formulas in A and B.

Collumn B contains this formula filled down
=IF(C3<>"None",D3,"Not Active")

Can anyone Help

Thanks in Advance

 

[barry houdini]

If you get zero for MIN even when the cells contain dates then probably those dates are text formatted - what do you have in columns D and E, are there formulas in those columns?

 

[clumsy-xceller]

Yes these cells are filled by a formula

=IF(F22<>"NONE",G22,"Not Active")

F22 is the Product collumn and G22 is the Date collumn.

I see what you are saying that when the argument <>"NONE" is met then the cell is filled by a text version of the date column. I have formatted the columns in date format but that didnt help either.

 

[clumsy-xceller]

Sorry just reread your reply,

D and E are linked to calendar dropdowns. so the cells are filled with dates selected from the calendar dropdown.

 

[clumsy-xceller]

If you get zero for MIN even when the cells contain dates then probably those dates are text formatted - what do you have in columns D and E, are there formulas in those columns?

Next Clue, I find If I enter a date manually into the cell that is linked to the dropdown date picker the rest of the formulas work fine. But if I use the date picker it doesnt and I am back to the 0 result

 

[barry houdini]

Try changing your column A and B formulas like this

=IF(C3<>"None",D3+0,"Not Active")

The +0 hould convert a text-formatted date to a true date, you might have to format the cells with the required date format

 

[clumsy-xceller]

Try changing your column A and B formulas like this

=IF(C3<>"None",D3+0,"Not Active")

The +0 hould convert a text-formatted date to a true date, you might have to format the cells with the required date format

Brilliant I was trying a macro to do the same job but couldnt manage it, thi does the trick perfectly

Thanks Alot