brett.white1
New member
- Joined
- Oct 28, 2016
- Messages
- 2
- Reaction score
- 0
- Points
- 0
I need to calculate the inclusive days remaining in the first month of a service period, the full months to the end of 30 Sep for each year, and the number of days of the last month.
The days need to be calculated on a 30-day period (days360) for each month.
The first month needs to be inclusive, i.e. a start date of 24 Feb 2016 would have 7 services days for the charge (24,25,26,27,28,29,30)
A start date of 31 Mar 2106 would be a single day of service (March = 30 days and is service is being provided on the last day of the month)
There are different services to be provided, each with a different rate:
N1 = 3.65 / unit
N2 = .0095 / unit
N4 = 195 / unit
N5 = .0023 / unit
N6 = .25 / unit i.e. (.25) x (Quantity) x (#ofDays in month 1) = N6 charge for 1st month period
I have tried using EOMONTH, DATEDIF and DAYS360 and can't find a formula that works for 28-day months as well as 30-day months
I greatly appreciate any help you can provide to figure this out!
Thanks!
The days need to be calculated on a 30-day period (days360) for each month.
The first month needs to be inclusive, i.e. a start date of 24 Feb 2016 would have 7 services days for the charge (24,25,26,27,28,29,30)
A start date of 31 Mar 2106 would be a single day of service (March = 30 days and is service is being provided on the last day of the month)
There are different services to be provided, each with a different rate:
N1 = 3.65 / unit
N2 = .0095 / unit
N4 = 195 / unit
N5 = .0023 / unit
N6 = .25 / unit i.e. (.25) x (Quantity) x (#ofDays in month 1) = N6 charge for 1st month period
I have tried using EOMONTH, DATEDIF and DAYS360 and can't find a formula that works for 28-day months as well as 30-day months
I greatly appreciate any help you can provide to figure this out!
Thanks!