+ Reply to Thread
Results 1 to 2 of 2

How to identify and count days between intervals

  1. #1
    Registered User
    Join Date
    01-25-2018
    Location
    Charlotte, NC
    MS-Off Ver
    2016
    Posts
    1

    How to identify and count days between intervals

    Hello all!

    So I am working on a project and I've hit a roadblock.

    What I want to do is find the number of days that a parcel (identified by 'PREM_CODE') had water service during a two year period.

    However, the structure of the data is giving me some issues. Each row in this table represents a water meter reading and whether the water was turned on or off. If the water was turned on, the value in the field 'ACTN_CODE' would be 'IN,' if the meter was being turned off, the value would be 'OUT.'

    There are a few assumptions that I need to put into a logic to deal with these that are giving me trouble. Those assumptions are:

    If a premises's (PREM_CODE) first chronological event is a turn-on event (i.e. 'ACTN_CODE'='IN'), then use that date should be used as the starting date (Below Example 89 and 99).

    If a premises's (PREM_CODE) first chronological event is a turn-off event (i.e. 'ACTN_CODE'='OUT'), then subtract the number of days between the period's first date (7/1/2015) to the date of the event (ACTION_DATE) (Below Example 50,76, and 152).

    Then, after that first logic is implemented, I need to count the days between any subsequent 'IN' and 'OUT' events.

    If a PREM_CODE's last entry is an 'IN' event, then I need to subtract that 'IN' event's date from the end date, which is 8/1/2017.

    And lastly, if a PREM_CODE's last entry is an 'OUT,' then no further calculations should be made.

    Here is a sample of what this could look like:



    **Start date = 7/1/2015
    **End date = 8/1/2017



    PREM_CODE ACTN_CODE ACTION_DATE
    50 OUT 7/30/2016
    50 IN 9/21/2016
    89 IN 10/10/2015
    76 OUT 12/1/2015
    76 IN 1/1/2016
    76 OUT 9/1/2016
    76 IN 5/5/2017
    99 IN 7/30/2015
    152 OUT 7/1/2017


    Each of these has its own unique situation.



    For PREM_CODE=50, the calulcation would be: (7/1/2015-7/30/2016)+(9/21/2016-8/1/2017)
    For PREM_CODE=89, the calculation would be: (10/10/2015-8/1/2017)
    For PREM_CODE=76, the calculation would be7/1/2015-12/1/2015)+(1/1/2016-9/1/2016)+(5/5/2017-8/1/2017)
    For PREM_CODE=99, the calculation would be: (7/30/2015-8/1/2017)
    For PREM_CODE=152, the calculation would be: (7/1/2015-7/1/2017)



    Any and all help would be appreciated!

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,197

    Re: How to identify and count days between intervals

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ 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. Replies: 4
    Last Post: 01-04-2013, 02:21 PM
  2. Distributings days across intervals
    By pacharbo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-01-2007, 03:57 PM
  3. Replies: 46
    Last Post: 09-06-2005, 07:05 PM
  4. Replies: 18
    Last Post: 09-06-2005, 07:05 AM
  5. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Domenic in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 09-06-2005, 04:05 AM
  6. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  8. [SOLVED] Count Intervals of Filtered TEXT values in Column and Return Count across a Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12: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