Sean15
Well-known Member
- Joined
- Jun 25, 2005
- Messages
- 698
- Office Version
- 2010
- Platform
- Windows
Hi:
Formula in column E is returning required values, except in E9. If start date in A9 falls within date month in D1, formula in E9 should return prorate. Also could formula be shortened as it is becoming long and complicated. Thanks for your help.
Formula in column E is returning required values, except in E9. If start date in A9 falls within date month in D1, formula in E9 should return prorate. Also could formula be shortened as it is becoming long and complicated. Thanks for your help.
Prorate month.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Date | 5/1/2022 | ||||||||
2 | ||||||||||
3 | Start | End | Paid | |||||||
4 | 01/01/22 | 04/28/22 | - | 1,200.00 | ||||||
5 | 02/25/22 | 03/25/22 | - | 1,100.00 | ||||||
6 | 02/01/21 | 03/01/22 | - | 1,500.00 | ||||||
7 | 02/15/22 | 04/15/22 | - | 1,600.00 | ||||||
8 | 03/01/22 | 05/01/22 | 40.32 | 1,250.00 | ||||||
9 | 05/15/22 | 08/12/22 | 750.00 | required value 350.00 | 750.00 | |||||
10 | 06/01/22 | 12/07/22 | - | 1,450.00 | ||||||
11 | 07/01/22 | 12/31/22 | - | 1,600.00 | ||||||
12 | ||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E4:E11 | E4 | =IF(B4<D$1,0,IF(A4>=EOMONTH(D$1,0)+1,0,IF(AND(B4>=D$1,B4<=EOMONTH(D$1,0)),(B4-D$1+1)/DAY(EOMONTH(D$1,0))*H4,H4))) |