ateeqsindhu
New Member
- Joined
- Jan 31, 2021
- Messages
- 10
- Office Version
- 365
- 2019
- 2016
- 2013
- 2010
- Platform
- Windows
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.
Book2.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | 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+) | ||
2 | 8400 | 38 | 11-Nov-19 | 105130028223 | -5 | |||||||
3 | 8437 | 38 | 12-Nov-19 | 105130028223 | -5 | |||||||
4 | 8468 | 38 | 13-Nov-19 | 105130028223 | -5 | |||||||
5 | 8532 | 38 | 15-Nov-19 | 105130028223 | -5 | |||||||
6 | 8558 | 38 | 16-Nov-19 | 105130028223 | -5 | |||||||
7 | 8584 | 38 | 17-Nov-19 | 105130028223 | -5 | |||||||
8 | 8609 | 38 | 18-Nov-19 | 105130028223 | -5 | |||||||
9 | 2053 | 38 | 18-Apr-19 | 105130028223 | 4 | |||||||
10 | 2100 | 38 | 20-Apr-19 | 105130028223 | 4 | |||||||
11 | 2146 | 38 | 21-Apr-19 | 105130028223 | 4 | |||||||
12 | 2196 | 38 | 22-Apr-19 | 105130028223 | 4 | |||||||
13 | 2364 | 38 | 26-Apr-19 | 105130028223 | 4 | |||||||
14 | 2404 | 38 | 27-Apr-19 | 105130028223 | 4 | |||||||
15 | 2444 | 38 | 28-Apr-19 | 105130028223 | 4 | |||||||
16 | 16880 | 38 | 17-Oct-20 | 105130028223 | 4 | |||||||
17 | 16891 | 38 | 18-Oct-20 | 105130028223 | 4 | |||||||
Sheet1 |