Results 1 to 3 of 3
  1. #1
    carlos
    Guest

    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. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

Posting Permissions

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