IF ELSE Usage

[L84THSKY]

Hello

I started a new job and I need help with my excel sheet. I have a column with dates. At the bottom I am using =MAX(F1660:F1680) to result the most recent date. Then I am using =DATEDIF(F1681,TODAY(),"D") to use the result of MAX date to count the number of days since the most recent date compared to the current date.

The problem is when there is no date (only blanks) in the column that MAX is choosing from, it defaults to 1/0/1900. Then the DATEDIF compares 1/0/1900 to the current date and spits out a number such as 43579.

How do I say.... IF MAX is "BLANK" output "" ELSE output MAX?

Or in the other section where DATEDIF is used............IF DATEDIF "greater than 1000" , "0", ELSE DATEDIF

Thanks
Eric

 

[Fluff]

Hi & welcome to MrExcel
How about
=IF(F1681=0,"",DATEDIF(F1681,TODAY(),"D"))

 

[L84THSKY]

Hi & welcome to MrExcel
How about
=IF(F1681=0,"",DATEDIF(F1681,TODAY(),"D"))

That worked?....thanks

 

[Fluff]

You're welcome & thanks for the feedback

 

[L84THSKY]

Hello again.

What about the MAX command still putting 1/0/1900 in the F1681 cell? How do I get MAX to go blank when all the cells above are blank?

Thanks
Eric

 

[Fluff]

Try
=IF(MAX(F1660:F1680)=0,"",MAX(F1660:F1680))
And
=IF(F1681="","",DATEDIF(F1681,TODAY(),"D"))

 

[L84THSKY]

Hello Fluff

When I used =IF(MAX(F2:F13)=0,"",MAX(F2:F13)) in a column with the highest date being 9/11/2018, it put 43354. It should have resulted in 9/11/2018
When I used =IF(MAX(F35:F46)=0,"",MAX(F35:F46)) in a column with all blank cells, it put 43354. It should have resulted in a blank space

I think it is just spitting out the 1/0/1900 it always did, just in a numeric result. But when it has values it now won't put the proper value.

Thanks

 

[MARK858]

What happens when you format the cell with 43354 as a date?

 

[L84THSKY]

What happens when you format the cell with 43354 as a date?

It is formatted as a date now.

 

[Fluff]

Is it all working now?