+ Reply to Thread
Results 1 to 5 of 5

Calculating the number of days between dates & lleaving the formula containing cell blank

  1. #1
    Registered User
    Join Date
    01-23-2014
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    Excel 2007
    Posts
    3

    Calculating the number of days between dates & lleaving the formula containing cell blank

    Hello all!

    I'm working on developing an annual housing report spreadsheet and I need to have an equation in AI3 that will calculate the number of days between AG3 (move in) and AH3 (move out). If there is no move out date yet, AI3 should use the current date to calculate how many days that person has been in housing (so it will automatically recalculate each day).

    Right now I have this equation entered in AI3

    =DATEDIF(AG3,TODAY(),"d")

    The issue is that when there is no date entered in AG3, "41662" is what appears in AI3. I need AI3 to remain at "0" until a date is put into AG3.

    Does anyone know what equation I would use?

    Thank you so much!

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Calculating the number of days between dates & lleaving the formula containing cell bl

    =IF(AG3="",0,DATEDIF(AG3,TODAY(),"d")) is one way
    mind you you dont need datedif for days
    =IF(AG3="",0,TODAY()-AG3)
    Last edited by martindwilson; 01-23-2014 at 05:46 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    01-23-2014
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Calculating the number of days between dates & lleaving the formula containing cell bl

    UPDATE: This works but it doesn't recalculate if there is a move out date in AH3. Is there a way to have this equation calculate the number of days up to today unless there is a move out date in AH3? If there's a date in AH3, it should just calculate the number of days between the two dates.
    Last edited by IntrntDetective; 01-29-2014 at 04:54 PM.

  4. #4
    Registered User
    Join Date
    01-23-2014
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Calculating the number of days between dates & lleaving the formula containing cell bl

    Does anyone know a solution to this?

    Thank you!

  5. #5
    Registered User
    Join Date
    11-20-2013
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Calculating the number of days between dates & lleaving the formula containing cell bl

    Few ways:

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    I also read that using the TODAY() function in large spreadsheets can bog excel down, so they recommend having a (1) cell somewhere with the TODAY() formula, and all the other formulas (like the above) reference that cell.
    Last edited by kspeese; 01-29-2014 at 05:50 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Seeking FOrmula for Calculating Number of Days between 2 Dates
    By TMack in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-18-2013, 01:32 PM
  2. Calculating number of days between two dates
    By Hblbs in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-07-2009, 11:32 AM
  3. Calculating the number of days between dates
    By Msann72 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-08-2008, 11:57 AM
  4. Replies: 5
    Last Post: 10-26-2005, 02:05 PM
  5. calculating number of days (e.g., Mondays) between two dates
    By Bernie Deitrick in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 09-06-2005, 07:05 AM

Tags for this Thread

Bookmarks

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