Evaluate three dates, return the greater date 3

[FYRGUY47]

Greetings,

In Excel 2002, I need to evaluate three dates in three seperate cells, A1, B1, C1 formatted as mm/dd/yy, and have the greater of the three dates displayed in the forth cell "D1"
Any help would be very much appriciated.

Chris

 

[TonyJollans]

Does [blue][tt]=MAX(A1:C1)[/tt][/blue] not work for you?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[FYRGUY47]

That worked perfect. Didn't know about the =Max.

Thank you

 

[FYRGUY47]

The =max works fine. I realized that I would like the cell to remain blank if no date is in any of the three cells as described above.

The formula worked until I wanted to leave it blank...

My formula looks as follows:
=IF('08Dec28'!G25="", IF('08Dec28'!F29="", IF('08Dec28'!F35="","", MAX('08Dec28'!G25,'08Dec28'!F29,'08Dec28'!F35))))

This returns a "FALSE" in the cell. I have tried everything I can think of, but feel I am just missing one small thing...

Thanks for your help..

Chris

 

[SkipVought]

Something like this...
[tt]
=IF(AND(A3=0,A4=0,A5=0),"",MAX(A3:A5))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

I will try that Skip.

Can you tell me what the "and" stands for or will do?

Chris

 

[SkipVought]

AND is a boolean operator.

Logical equivalence...
[tt]
AND(A3=0,A4=0,A5=0) == A3=0 AND A4=0 AND A5=0
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

Skip,

The formula I changed it to is this:
=IF(AND('08Dec28'!G25=0,'08Dec28'!F29=0,'08Dec28'!F35=0),"", MAX('08Dec28'!G25,'08Dec28'!F29,'08Dec28'!F35))

It returns "01/00/00". I am referencing the earlier sheet in these examples (which I know you can see). Still having problems.

Looking at my formula, it is as close to yours as I can see. Do you see anything I am missing..?

Chris

 

[SkipVought]

are you sure that you have CALCULATED (f9)? Seems like your Calculate is set to MANUAL.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

Sorry Skip,

You lost me. F9 is not a cell I am looking at.

MANUAL = I am moving to the previous sheet and clicking on the three cells I need manualy...

Chris

 

[SkipVought]

F9 == Function Key 9 which is CALCULATE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

Is there something somewhere I can read up on? I do not use the function keys for anything like that...

Chris

 

[SkipVought]

Check out Excel HELP on Keyboard shortcuts

But the question at hand is, did you CALCULATE you sheet? If you did, the formula would not return a ZERO (which is FORMATTED as date 01/00/00)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

I guess I didn't, How do I do that? I am not even sure what you mean..

Unsure
Chris

 

[SkipVought]

Hit your F9 key, just above the 8 on your keyboard.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

Hi Skip,

In your earlier post tonight, when I realized you were talking about the F9 key, I did hit it a couple of times with no noticable changes. I will read up on the keyboard short cuts and see if I can make since of it.

I do use the "F" keys in an AutoCAD program I use, however never have used them in Excel.

So, you are saying the F9 key will calculate the entire sheet?

Chris

 

[GSCaupling]

Hi Chris,

Open the sheet in question and press the F9 key on your keyboard. This will cause all formulas in your sheet to recalculate.

Also, if all of this is on one sheet, you don't need the sheet reference (08Dec28) in your formula. Just use the cell references: G25, F29 and F35. It's simpler.

By the way, what's the name of the class you're taking, anyhow?

GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[SkipVought]

yes.

What happend if your EDIT your formula (select that cell & F2) then hit ENTER. You should never get 01/00/00, with the formula you last posted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

Skip,

I did select the cell, hit F2, then hit enter. Same outcome = 01/00/00

GS,

It is all in one workbook that will have about 9 sheets (27 day cycles), that is why I need to refrence back to keep things carring over from one 27 day cycle to the next (pay cycles) I am not taking any class at this time, however I have taken some advanced class' in specificly Excel, I work for a fire department, I design and maintain sheets similar to time sheets but now they are adding more items to track on the same sheet.

Still do not see any changes when I press the F9...


Thanks guys,

Chris

 

[macropod]

Hi Chris,

If your date cells are A1:A1, you could use:
=IF(MAX(A1:C1)=0,"",MAX(A1:C1))

As for the values not recalculating, are you sure the:

1. dates have been stored as dates and not as text strings? To make sure, select a cell with a date. Choose Data|Text to Columns. Click Next, Next. Choose 'Date' and an appropriate date format from the Date dropdown. Click Finish. Repeat for the other date cells.

2. cell into which you're inserting the formula is not formatted as text? To make sure it's not, select the cell and choose Format|Cells|Number, click 'Date' and choose an appropriate date format.

Cheers

[MS MVP - Word]