comparing and choosing date out of 2 cells

Microsoft Excel 2007
December 9, 2009 at 11:57:49
Specs: Windows XP
I've tried to write a formula that compares two cells containing a date in each and then selecting the larger/later date into a third cell but so far, no other googled suggestions are working. I started with
=if(a1>b1, a1, b1)
both cells are formatted with date (mm/dd/yyyy) and the 3rd cell is formatted to turn out (mmyyyy) can you help?

See More: comparing and choosing date out of 2 cells

Report •

December 9, 2009 at 12:31:45
So what seems to be the problem?

As far as I can tell, your formula does what you want:

A1 = 1/2/2006
B1 = 6/2/2007

=IF (A1>B1,A1,B1) will return 062007 based on the format you've given us.

Report •

December 9, 2009 at 13:16:48

I don't know what isn't working for you. As DerbyDad03 said, the formula looks fine and works.

I wonder if the date input in cells A1 and B1were not recognized by Excel as dates.

When you enter data into a cell Excel tests it to see if it is a date. If Excel doesn't recognize the input as a date, it displays it just as you entered it.

If it recognizes the entry as a date it converts the information to a number which is stored and which will be used for your comparison, but it displays the information in a date format.

To test if A1 and B1 contain real date information, try formatting each cell with different date formats - Format - Cells - Number Tab. Select different Date formats. If the cell does not change to match the different formats you select, Excel has not recognized the entry as a date, and you are comparing two pieces of text!

If this is the case try re-entering the dates as 01-Jan-2009 for example, so that there is no ambiguity and this is a format Excel recognizes.

Also if a cell shows "######" it may not be wide enough to display the date in the selected format, -try widening the column.


Report •

December 10, 2009 at 11:13:46
The cell is not reflecting a date - it's just showing the formula and then when I hit enter after putting the formula in, it says "error"....

Report •

Related Solutions

December 10, 2009 at 11:17:35
Here's what happened when I retried again...

40035 2/10/2009 8/10/2009

obviously, it should reflect 082009....

Report •

December 10, 2009 at 11:42:42
40035 is Excel's numerical equivalent of 8/10/2009.

All dates in Excel are stored "internally" as numbers and it is only the formatting that makes them look like dates.

When I select the cell that contains 40035 and set the format to Custom mmyyyy, it displays 082009.

Now, if the cell for some reason had previously been formatted as text, and it is stuck that way, it might not change, regardless of what you try to set the format as. Excel is funky like that. Try this to see if it helps:

Select an empty cell.
Copy it.
Select the cell with 40035
Do an Edit...Paste Special...Operation: Add

This will add 0 to the contents and force it to be a number.

Now set the format to Custom mmyyyy and see what happens.

Keep in mind that I'm basing this on the simple IF formula you posted earlier. If you are doing something different, we need to know that.

Report •

Ask Question