Date formula returns 1900-01-00

[Dian Ferrell]

What is wrong with my formula? It works for information in columns C & D, but Column B returns the funny date.

 

[JoeMo]

Nothing wrong with your formula. In the cells filled with red, the formula returns C5 to C8 all of which are blank and seen by Excel as having a value of 0. That date serial number when formatted as a date "yyyy-mm-dd" is 1900-01-00.

 

[Joe4]

Welcome to the Board!

There is nothing wrong with it. You just need to understand how Excel tracks dates.
They actually store dates as numbers, specifically, the number of days since 1/0/1900.
So a date is really just a number with a date format.
So a value of 0 will look like 1900-01-00 in your date format.

The question then becomes. what do you want returned when BOTH D4 and C4 are blank? The value in B4 or a blank?

 

[Dian Ferrell]

Yes the value in B4

 

[Joe4]

OK, assuming that at least one of columns B, C, and D have a value, try this:
=IF(D4>1,D4,IF(D4="",IF(C4="",B4,C4)))

 

[Dian Ferrell]

That works to return the value in B4, but messes up another part of the formuala. I need my formula to look at column D and return that value, if blank look at column C and return that value, but if blank return the value in column B.

 

[Joe4]

That works to return the value in B4, but messes up another part of the formuala. I need my formula to look at column D and return that value, if blank look at column C and return that value, but if blank return the value in column B.

That is exactly what my formula does.
Can you explain an example where it does not work?

 

[Dian Ferrell]

Your formula was perfect! Thank you so much, it's been a long day.

 

[HappierThan]

Did the formula in E2=IF(D2>1,D2,IF(D2="",B2,C2), and then you filled down? If so perhaps it should have needed some referencing along the lines of:-E2=(IF($D$2>1,$D$2,IF($D$2="",B2,C2)

 

[Joe4]

Your formula was perfect! Thank you so much, it's been a long day.

You are welcome.