excel question - need help with a formula

[Babs]

we have a spreadsheet for calculating information on clients here at the shelter. our problem is this, we need a formula to calculate how long each person has been staying in the shelter that will update each day. that part we have. what we need is how to write a formula for empty cells in that column that wont add data until you actually put a client in that cell.

when i put our formula into the blank cells it automatically calculates from 1900 or something lol so it makes it look like that client was here when moses was here lol

here is the formula that works for us in cells being used:

=DateDif (cell,cell,"D")

is there a way to write this formula so it wont actually calculate until there is information put into the cell? right now we are having to copy and paste the formula each time a new client comes in.

thanks

[Scott]

I think you just may have to add the row each time a new client is set up, then copy and paste. We do that all the time on our sheets.

Check with Seth, he is pretty good with formula's, he has helped me in the past.

[Babs]

yeah thats what we are doing now but there is only two of us that are savvy enough to even do that lol

[SippinTea]

Chel would be another one that might be able to help. She's bailed me out a few times.

[Scott]

I'd almost have to look at the spreadsheet. 

Have  you considered adding about 20 rows, the special pasting the formulas?

[Babs]

yes but when i did it, now its adding time up since the early 1900's lol, if the cell is blank it calculates from there. i know there is a way to put it so it wont calculate until there is actually some information in there but cant think of it lol


if i need to i can email a copy to someone and see if they can figure it out even

[Scott]

put zero's in the blank cells- the formulas look for numbers not blanks

[Chseeads]

Did you figure anything out yet?

[Babs]

not really seth, our formula works for the most part, but what is messing up is the blank cells in that column, i was trying to figure out a formula that would not do anything if the cell was blank. its the form they keep the census on, so its not feasible to put zeros in the field since its an unending form

[Scott]

Blank cells will always mess up the formula.

[Chseeads]

Is there any way to do some kind of "if" statement to take the blanks into account?

I'm really not all that great with all those complex formulas. lol

[Babs]

i was thinking there was an if statement but cant remember how to use it. obviously i'm not any better than seth at it lol

[Scott]

i was thinking there was an if statement but cant remember how to use it. obviously i'm not any better than seth at it lol

I think you have to have something ''if blah blah > 0 then blah blah blah''

[Babs]

well blah why couldnt i think of that? lol blah!

[The Purple Fuzzy]

That's why it's good to have smart friends.

[Babs]

That's why it's good to have smart friends.

[Geri]

I think you can use "not equal to blank" logic in the if statement as well, but it's been so long since I have messed in excel that I'm not sure if the exact syntax. I could tell ya in VB! lol

[Babs]

lol vb would prolly be easier lol

but i will try the blah and not equal to and let ya know. thanks everyone!

[Q-tip]

I don't know if this will help you or not but I use the "if Iserror" function when I don't want results returned on empty cells.  

This is one of the formulas used:


=IF(ISERROR(AVERAGE(B11:O11)),"",AVERAGE(B11:O11))

if i need to i can email a copy to someone and see if they can figure it out even

If this does not help I would love to take a stab at it for you.  Send me a copy.







God Bless.

Chris.

[Babs]

i will try that q-tip and let you know. thank you all so much!

[Q-tip]

Without having seen the spreadsheet here are two possible options:

1)  In the cell for the beginning date enter the formula to enter todays date automatically(=today() ).  This will return a value of 0 for the difference between the dates.  When someone new is entered just type that date in the cell and it will remove that formula.  Some conditional formatting may be required if you are seeking blank cells.

2)  First create a new range of cells involved in your process.  Click on the cell for the beginning date and hit the space bar instead of delete.  Hitting Pdelete will cause the formula for the date difference to reference the default date setting of 1900 or 1904 giving you a result of something like 40,000.  Pressing the space bar will overwrite the default date and return a result like #value!. 

If this result is undesired or interferes with other formulas then enter the following formula in the cell for the date difference.

=if(iserror(datedif(cell,cell,"d")),"",datedif(cell,cell,"d"))

This will return a blank cell unil a beginning date is entered as long as the beginning cell is spaced rather than deleted.



There is probably an easier way to do it but this is all I can come up with now.

Hope this helps.

God Bless.

Chris.

[Q-tip]

Here is the formula you are looking for:

=IF(A1="","",DATEDIF(A1,B1,"d"))


A1 being the start date and b1 beign todays date.



God Bless.

Chris.

[Babs]

thanks so much Chris, the lady that is needing this will be back sunday night and we will use it. 

[Babs]

worked perfectly Chris!!! thank you so much!!!