1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

help needed with excell formula

Discussion in 'Business Applications' started by rama4672, Oct 6, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. rama4672

    rama4672 Thread Starter

    Joined:
    Jun 1, 2003
    Messages:
    84
    What i want to do is take a start date and then get it to count the number of days between the end date

    IE: A1 would be 12/09/03 A2 would be the end date and A5 would hold the formula.
    So if a chart was issued on 12/09/03 it would count the number of days and display in A5 until the chart was returned and the date of return was put in col A2 then A5 Should be blank.


    Tia
    Rama
     
  2. kiwiguy

    kiwiguy

    Joined:
    Aug 17, 2003
    Messages:
    17,584
    In A5 simply subtract A1 from A2.
    Format A5 as a number, not as a date.

    Excel handles date arithmetic fine, subtracting 1 date from another gives the number of days.

    Just re-format the cell where the formula is, as it will take on a date format by default

    I cant quite comprehend the fine detail you want following that.
     
  3. kiwiguy

    kiwiguy

    Joined:
    Aug 17, 2003
    Messages:
    17,584
    In A5:

    =IF(A2="",INT((NOW()-A1)),"")

    Will put the number of days between the date in A1 and NOW(), being todays date and time.

    As soon as A2 has any entry in it, A5 goes blank.
     
  4. rama4672

    rama4672 Thread Starter

    Joined:
    Jun 1, 2003
    Messages:
    84
    thanks for the quick reply. but there is no date in A2 to take away, the charts are issued to a driver and the date put in col A1 then they have 21 days to return the charts back to us, so it must calculate how many days has passed since being issued with the chart.
    When the chart is returned we then put the return date in A2 and then A5 should be blank so that we know that the chart has been returned

    Tia

    Rama
     
  5. rama4672

    rama4672 Thread Starter

    Joined:
    Jun 1, 2003
    Messages:
    84
    thanks for the reply kiwiguy it seems you sent your second reply as i was replying to your first

    Thanks again

    Rama
     
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    =IF(ISBLANK(A1),"",NOW()-A1)

    It probably does the same as KiwiGuy, but might be easier to understand for us non-mathematicians.

    Sorry, kiwiguy. I feel like I'm picking on you! I'm not! I swear!
    :D
     
  7. kiwiguy

    kiwiguy

    Joined:
    Aug 17, 2003
    Messages:
    17,584
    Your answer may give unwanted decimal places, as NOW() includes the time.

    =IF(ISBLANK(A1),"",INT(NOW()-A1)) may work better. (E&OE)

    You picking on me?
    Never.
    Kiwis may be endangered species, but they have a sense of humo(u)r and a thick skin.

    Cheers
     
  8. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    ROFL!!

    For the record: formatting the cell gets rid of unwanted decimals too, and he doesn't want to return a date/time, he needs a number of days.

    More importantly, when I was in the Navy, I took a "jump test" so I could go flying from time to time with my bosses, who were pilots. We had to jump out of this dead airplane and onto a trampoline-kinda-thing. Really stupid. But it was called the Kiwi test. :) I got to go flying twice in a T34-C. WAY cool. And I did not get sick!
     
  9. kiwiguy

    kiwiguy

    Joined:
    Aug 17, 2003
    Messages:
    17,584
    Because Kiwis dont fly I guess?

    We do that a lot down here, but with bungy cords attached.
    No wimpy Trampolines!

    Now, jumping out of a flying plane onto a trampoline, thats more likely to be an achievement.

    You would make your mark that way.
     
  10. rama4672

    rama4672 Thread Starter

    Joined:
    Jun 1, 2003
    Messages:
    84
    thanks for the input guys... that works OK but when a date is added in to cell A2 i need the cell that holds the formula to be left blank


    Tia

    Rama
     
  11. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    Dreamboat and the Flying Kiwi's:

    What is the world's preocupation with NOW ? I hate using it unless I have to have the time. Why not DBAs formula:

    =IF(ISBLANK(A1),"",TODAY()-A1)


    and Rama....what you you mean by the last response?
    The above formula should leave cell A3 blank until a date is added. What is it then that you want?

    I was understand - but now are cornfused!

    - Castleheart :confused:
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/170067

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice