+ Reply to Thread
Results 1 to 5 of 5

Star (Min) and End (Max) date claculation from series of dates in table

  1. 01-31-2021, 05:40 AM #1
    ateeqsindhu
    ateeqsindhu is offline
    Registered User
    Join Date
    01-31-2021
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    2

    Star (Min) and End (Max) date claculation from series of dates in table

    Facing trouble calculating Star(Min) and End(Max) dates from a table so a to show start and end date of a closing balance amount appeared in respective GL. this is a minute extract of huge data set. also need to calculate duration (days) from start to end and ageing of same.

    Sr. No. Branch Code Date GL Code Closing Balance Start End "Days
    (no. of days from start till end)" Count Trns.(count of unique Closing Balances) Ageing (w.r.t. how many days closing balance remained in this table, slabs are 0-30days, 31-60days, 61-90days, 91-180days & 18+)
    8400 38 11-Nov-19 105130028223 -5
    8437 38 12-Nov-19 105130028223 -5
    8468 38 13-Nov-19 105130028223 -5
    8532 38 15-Nov-19 105130028223 -5
    8558 38 16-Nov-19 105130028223 -5
    8584 38 17-Nov-19 105130028223 -5
    8609 38 18-Nov-19 105130028223 -5
    2053 38 18-Apr-19 105130028223 4
    2100 38 20-Apr-19 105130028223 4
    2146 38 21-Apr-19 105130028223 4
    2196 38 22-Apr-19 105130028223 4
    2364 38 26-Apr-19 105130028223 4
    2404 38 27-Apr-19 105130028223 4
    2444 38 28-Apr-19 105130028223 4
    16880 38 17-Oct-20 105130028223 4
    16891 38 18-Oct-20 105130028223 4
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-31-2021, 05:54 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Star (Min) and End (Max) date claculation from series of dates in table

    Welcome to the forum.

    Fill in the first three rows with manually calculated results to illustrate what you are expecting, please, and then post the workbook again.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 01-31-2021, 06:05 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Star (Min) and End (Max) date claculation from series of dates in table

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new, I will provide the link for you this time: https://www.mrexcel.com/board/thread...table.1160163/.)
    Reply With QuoteRegister To Reply
  4. 01-31-2021, 09:22 AM #4
    ateeqsindhu
    ateeqsindhu is offline
    Registered User
    Join Date
    01-31-2021
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    2

    Re: Star (Min) and End (Max) date claculation from series of dates in table

    AliGW

    I've prepared requirement sheet with possible solutions kindly guide me with formula/VBA to solve this issue;

    "unable to attach the file"

    Sr. No. Branch Code Date GL Code Closing Balance Start End "Days
    (no. of days from start till end)" Count Trns.(count of unique Closing Balances) Ageing (w.r.t. how many days closing balance remained in this table, slabs are 0-30days, 31-60days, 61-90days, 91-180days & 180+[/B])
    8400 38 11-Nov-19 105130028223 -5 11-Nov-19 18-Nov-19 8 1 0-30Days
    8437 38 12-Nov-19 105130028223 -5 11-Nov-19 18-Nov-19 8 0-30Days
    8468 38 13-Nov-19 105130028223 -5 11-Nov-19 18-Nov-19 8 0-30Days
    8532 38 15-Nov-19 105130028223 -5
    8558 38 16-Nov-19 105130028223 -5
    8584 38 17-Nov-19 105130028223 -5
    8609 38 18-Nov-19 105130028223 -5
    2053 38 18-Apr-19 105130028223 4 18-Apr-19 28-Apr-19 11 1 0-30Days
    2100 38 20-Apr-19 105130028223 4
    2146 38 21-Apr-19 105130028223 4
    2196 38 22-Apr-19 105130028223 4
    2364 38 26-Apr-19 105130028223 4
    2404 38 27-Apr-19 105130028223 4
    2444 38 28-Apr-19 105130028223 4
    16880 38 17-Oct-20 105130028223 4 17-Oct-20 18-Oct-20 2 1 0-30Days
    16891 38 18-Oct-20 105130028223 4
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-31-2021, 09:35 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Star (Min) and End (Max) date claculation from series of dates in table

    Did you understand post #3? You seem to have ignored it - I'd appreciate your acknowledging it at least. You should know that Mr Excel has the very same cross-posting rule, so you should be posting a link to this thread over there, and I see you haven't done so yet.
    Last edited by AliGW; 01-31-2021 at 09:38 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel Formula to find a date that is within 20 days of a series of dates.
    By taylorsm in forum Excel General
    Replies: 3
    Last Post: 05-17-2017, 12:28 PM
  2. Add dates to date series for those dates which have no data
    By ddonham in forum Excel - New Users/Basics
    Replies: 12
    Last Post: 05-01-2017, 02:37 PM
  3. [SOLVED] Add hour to star date&time
    By aganesan99 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2014, 03:39 AM
  4. Reading date from a cell with series of dates
    By bruce71101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-13-2010, 03:40 AM
  5. Calculate next date in series of dates
    By led_blunt in forum Excel General
    Replies: 5
    Last Post: 09-24-2009, 02:23 PM
  6. [SOLVED] Claculation in left corner
    By James McDowell in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-08-2005, 12:35 PM
  7. time sheet claculation
    By Frantic3d in forum Excel General
    Replies: 4
    Last Post: 07-01-2005, 07:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1