Calculating prorated value for each month based on date range
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks @PangHC & @Christina_H ..These solutions works great for me. Appreciate your help🙂
