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

 

[L84THSKY]

Is it all working now?

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.

 

[Fluff]

All you've done is re-post post#7.
Yet if you have formatted the cell as a date then this is no longer relevant.

Also what you have posted does not make sense as 43354 is not 1/0/1900
Therefore if you are still having problems, can you please let us know what those problems are?

 

[Indystick]

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.

43354 is the serial date value for 9/11/2018. Change your cell format from General/Number to Short Date.

 

[L84THSKY]

Is it all working now?

Yes......The problem I mentioned was because when I inserted your new code, the cell defaulted back to number, when it was orginially a date. I set it back to date and it works.

Now there is an unintended consequence that I hope to resolve. I wan't to be able to cut and paste that formula for all 250 records. The problem is, not all the records have the same # of rows in them, so there isn't an exact pattern to the cells.

Will I have to cut and paste the formula into all 250 records, and keep re-inputting the proper cell group for each record?

Thanks

 

[Fluff]

Without knowing what your data is like, it's difficult to say.
As I'm more VBA based than formulae based It would probably be best to start a new thread for this, explaining what your data is like.

 

[L84THSKY]

Without knowing what your data is like, it's difficult to say.
As I'm more VBA based than formulae based It would probably be best to start a new thread for this, explaining what your data is like.

It's just more time , but not a huge deal. The important stuff you solved....Thanks for the help; this site is awesome.

 

[Fluff]

You're welcome & thanks for the feedback