1/0/1900 Problems...

[cafesbk]

As I am new to the forum I am hoping that someone might be able to give me some assistance. I am not an Excel "expert" and know enough to get me in trouble. I am working on a spread sheet and have come up with the following formula that works for what I need it to do, but the formula displays the date as 1/0/1900. I have searched many sites looking for an answer as to how to modify this formula to present either a blank or a 0. I have tried several suggestions with no luck. Any assistance would be greatly appreciated!

For some additional details: This formula is pulling information off of another sheet and displaying it on this sheet.

=INDEX('Active Project Details'!$F$17:$F$1500,19*(ROWS($A$1:A1)-1)+1)

Thank you!

 

[Marcílio_Lobão]

cafesbk, Good evening.

Format this cell as Number.
Possibly this cell is formated as DATE.

Is that what you're looking for?
I hope it helps.

----------
Belo Horizonte, Brazil
Marcílio Lobão

 

[cafesbk]

As I am new to the forum I am hoping that someone might be able to give me some assistance. I am not an Excel "expert" and know enough to get me in trouble. I am working on a spread sheet and have come up with the following formula that works for what I need it to do, but the formula displays the date as 1/0/1900. I have searched many sites looking for an answer as to how to modify this formula to present either a blank or a 0. I have tried several suggestions with no luck. Any assistance would be greatly appreciated!

For some additional details: This formula is pulling information off of another sheet and displaying it on this sheet.

=INDEX('Active Project Details'!$F$17:$F$1500,19*(ROWS($A$1:A1)-1)+1)

Thank you!

The cell is formatted as a "Short date" as the information it is pulling is a date. If no date is filled in on the accompanying sheet I would like this cell to display either "o" or just blank.

 

[Diego_]

That 1/0/1900 really looks like a 0 (zero) formatted into short date.

When you say "no date is filled in the accompanying sheet", do you mean it's a blank cell?

If that's the case have you tried simply wrapping an IF around your "index" that returns blank if the value it is picking up is actually a blank?

=IF(INDEX('Active Project Details'!$F$17:$F$1500,19*(ROWS($A$1:A1)-1)+1)="","",INDEX('Active Project Details'!$F$17:$F$1500,19*(ROWS($A$1:A1)-1)+1))

 

[cafesbk]

That 1/0/1900 really looks like a 0 (zero) formatted into short date.

When you say "no date is filled in the accompanying sheet", do you mean it's a blank cell?

If that's the case have you tried simply wrapping an IF around your "index" that returns blank if the value it is picking up is actually a blank?

=IF(INDEX('Active Project Details'!$F$17:$F$1500,19*(ROWS($A$1:A1)-1)+1)="","",INDEX('Active Project Details'!$F$17:$F$1500,19*(ROWS($A$1:A1)-1)+1))

That did the trick! Thank you for your assistance! I really appreciate it!