# Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.

1. ## Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.

I am building a spreadsheet to calculate dates and expense.. IE. I place a rental building on a location on 01/01/2001 and it is there until 01/28/2001. I charge \$70 a day for it.

I currently have

Column 1 : Shack ID
Column 2: Start Date
Column 3: End Date
Column 4: Total Days

Then at the very end of Column 4 it calculates the # of days multiplied by the Charge per Day, so I that automatically know the revenue generated by the building. Problem I am having is. When Column 2 and 3 are empty column 4 results "0" which is fine with me. But IF only one of the columns, Column 2 or Column 3 have a date entered, Column 4 shows something similar to "47853" and completely screws up the Formula at the bottom of Column 4 calculating the revenue.

I know i've done this before, but I absolutely have racked my brain and Can't remember how to make it quit doing that. Any help is greatly appreciated.

Thanks All!

2. ## Re: Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.

It would help to see the formulas..

Can you post a sample book?
Click "Go advanced" at the bottom right of the reply window
Then click the Attachments Icon (looks like a paperclip)

3. ## Re: Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.

Can you see if this attached? The tabs at the bottom that say Shack 6, Shack 7, Etc Etc. is where i need this to work.

4. ## Re: Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.

Try this in J19

=IF(COUNT(D19:I19)=2,G19-D19,"")

This returns a Blank if both dates are not entered.
If you prefer the 0, change "" to 0

5. ## Re: Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.

I did that, but when I complete the formula, But having dates for both From date and To Date. The #of Days gives me the jacked up results now.

6. ## Re: Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.

What exactly does "jacked up" mean?
I assume you mean you're getting the wrong results.
Can you give a few examples of From/To Dates and the results you got with the new formula, and the results you expected ?
or
Can you post another book showing the "jacked up" results?

7. ## Re: Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.

Guard Shack Tracking and Summary.xlsx

I did your revision in Row 22;

8. ## Re: Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.

Well maybe not.. I see a mistake I made.

9. ## Re: Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.

Hey, That worked. I didn't realize I had one cell wrong in the forumla. It's working as needed.

Thanks so much !

10. ## Re: Forumula Results Incorrect when data missing. Wanting to hide the incorrect formula.

Not quite
I posted this
Originally Posted by Jonmo1
=IF(COUNT(D19:I19)=2,G19-D19,"")
You put this in the cell
=IF(COUNT(D22:I22)=2,G22-G22,"")

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1