# Thread: Help with IF formula (Excel 97 SR2)

1. ## Help with IF formula (Excel 97 SR2)

I am trying to work out a formula for working out the number of nights a dog has been in out adoption centre for. Col B will have the date the dog came into the centre for, and column c the date the dog left us.

The problem is I am only interested in the nights for the previous month. The form is completed on the 1st of the month, so If the dog has not been homed the answer will be 28 / 30/ 31. Ie the number of days in the previous month

However if a dog came in on Jan 1st and went out on July 28 the answer for July would be 28. and not almost 200.

Yes I know I can manually enter each line based on the above conditions, but we have almost 1000 dogs so you see why I want to automate process. I am happy to add a hidden columns or anything that makes process easier

Can anybody suggest a solution. I am happy to explain more fully if need be. I am based in the UK

Thank you

Carl Cross The Blue Cross Animal Welfare Charity

2. ## Re: Help with IF formula (Excel 97 SR2)

I'm not sure I fully understand what you need, but the following formula might apply :-<pre>=C1-(MAX(B1,DATEVALUE("1/"&MONTH(NOW())-1&"/"&YEAR(NOW()))))+1</pre>

It assumes that the column C contains a date in the previous month (if th e date is more tahn 1 month, it will return minus values).

If it is not suitable (check it for a number of scenarios for which it might be used) post back.

Andrew C

3. ## Re: Help with IF formula (Excel 97 SR2)

If C1contains the date the dog left, then the following formula should give what you want.

<pre>=INT(B1-DATE(YEAR(B1),MONTH(B1),1))
</pre>

#### Posting Permissions

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