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.

Excel date formula

Discussion in 'Business Applications' started by finknottle, Feb 14, 2008.

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

    finknottle Thread Starter

    Joined:
    Feb 14, 2008
    Messages:
    2
    I have an excel spreadsheet with rows of service event start and end dates, grouped by id number:
    1)12345
    2008/01/05 2008/01/06
    2008/01/12 2008/01 14

    2)54321

    2008/02/01 2008/02/10
    2008/02/02 2008/02/02
    2008/02/03 2008/02/03
    2008/02/11 2008/02/14
    I need to calculate the number of days between the end date and the next service start date - example 1 above is straightforward as it's A3-B1: 2008/01/12 - 2008/01/06

    Example 2 gets trickier because the first service event spans 9 days, and other service events occur within that period - I need to find the difference between the last event date (2008/02/10) and the next one after that - (2008/02/11). In my 5 full worksheets of data, the number of rows between the valid entries is not constant - it could be the third row or the thirteenth. Does anybody have any ideas? many thanks!!!!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I think that this is going to take a VBA Macro, (the Excel gurus might prove me wrong).
    With a macro you can loop through the whole worksheet and also loop through each ID Number set of data.
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Take a look at this sheet and see if something along these lines would work.
    However, it is just for one ID number. There may be a way to group tis using an IF statement also - IF the IDs are the same, then do this.
     

    Attached Files:

  4. finknottle

    finknottle Thread Starter

    Joined:
    Feb 14, 2008
    Messages:
    2
    I'm liking this - I think I can work with that idea using a couple of nested ifs to loopp through each record. I will give it a whirl and let you knowif it works. Many thanks!
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    If you get it figured out, please post a sample so we can see what you ended up doing...just curious. :)
     
  6. 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/683220

  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