Sean15
Well-known Member
- Joined
- Jun 25, 2005
- Messages
- 698
- Office Version
- 2010
- Platform
- Windows
Hi,
I am trying to calculate prorate amounts where applicable in column L. So, I’m looking for a formula that looks at Date in H1, if Date in range H3:H8 is less than H1, return zero, if Date in range H3:H8 falls with MONTH (H1), return prorate of amount in column J, all else return value in column J.
Thanks for your help.
Regards,
Sean
I am trying to calculate prorate amounts where applicable in column L. So, I’m looking for a formula that looks at Date in H1, if Date in range H3:H8 is less than H1, return zero, if Date in range H3:H8 falls with MONTH (H1), return prorate of amount in column J, all else return value in column J.
Book1 | ||||||||
---|---|---|---|---|---|---|---|---|
H | I | J | K | L | M | |||
1 | 5/1/2022 | Required | ||||||
2 | value | |||||||
3 | 07/05/22 | 1,575.00 | 1,575.00 | |||||
4 | 08/16/22 | 1,628.00 | 1,628.00 | |||||
5 | 09/25/23 | 4,582.00 | 4,582.00 | |||||
6 | 05/12/22 | 3,575.00 | 2,191.13 | date in H6 in within current month, so prorate 19 days | ||||
7 | 05/30/22 | 2,572.00 | 165.94 | date in H7 in within current month, so prorate 2 days | ||||
8 | 04/15/22 | 1,452.00 | N/A | date is less than H1, return zero | ||||
9 | ||||||||
10 | ||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L3:L5 | L3 | =J3 |
L6 | L6 | =19/31*J6 |
L7 | L7 | =2/31*J7 |
Thanks for your help.
Regards,
Sean