Hi Team, I have below table,
| AMOUNT | StartDate | EndDate |
| 2000 | 6/11/2023 | 7/31/2023 |
| 2500 | 7/1/2023 | 7/31/2023 |
| 5000 | 5/21/2023 | 7/31/2023 |
| 4000 | 4/14/2023 | 7/31/2023 |
Need the output as below. Now, first step is to check number of months between StartDate and EndDate. Then calculate days worked in all the months by the employee. For example the 3rd line item start date is 5/21/2023 so number of days worked by employee is 11. Similarly for June its 30 and July its 31. Then I'm calculating the prorated amount for each month and adding up at the end. Now the requirement here is how do I pick up number of months between StartDate and EndDate and also month Name or month number to extract total number of days in that month. As of now I have fixed the max number of month between dates to 4 but it might change. Appreciate any help around this. Apologies if I'm not very clear with the requirements
Output:
| THIS Column is not required as output. Just for your understanding | AMOUNT | StartDate | EndDate | DaysMonth1 | DaysMonth2 | DaysMonth3 | DaysMonth4 | ProratedMonth1 | ProratedMonth2 | ProratedMonth3 | ProratedMonth4 | Total Amount |
| Calculating working days for 2 months June and July | 2000 | 6/11/2023 | 7/31/2023 | 20 | 31 | | | 1333.33 | 2000.00 | | | 3333.33 |
| Calculating working days for 1 month July | 2500 | 7/1/2023 | 7/31/2023 | 31 | | | | 2500.00 | | | | 2500.00 |
| Calculating working days for 3 months May, June and July | 5000 | 5/21/2023 | 7/31/2023 | 11 | 30 | 31 | | 1774.19 | 5000.00 | 5000.00 | | 11774.19 |
| Calculating working days for 4 months April, May, June and July | 4000 | 4/14/2023 | 7/31/2023 | 17 | 31 | 30 | 31 | 2266.67 | 4000.00 | 4000.00 | 4000.00 | 14266.67 |
