Calculate days and cost

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 EDATE function works with months of any length - have a look at that. For example, with a base date of 31/01/2016 in A1, you will get the following:

=EDATE(A1,1) = 29/02/2016
=EDATE(A1,2) = 31/03/2016
=EDATE(A1,3) = 30/04/2016

and so on.
 
Thanks, but I am looking more more for a count of how many inclusive days remaining in the first month for the requested start date:

02 Feb 2016 = 29 service days (2 - 30 inclusive)
26 Feb 2016 = 5 service days (26 -30 inclusive)
29 Feb 2016 = 2 service days (29 -30 inclusive)
26 Mar 2016 = 5 service days (26 - 30 inclusive)
26 Apr 2016 = 5 service days (26 - 30 inclusive)
 
AB
102-Feb-1628
226-Feb-164
329-Feb-161
426-Mar-166
526-Apr-165

Spreadsheet Formulas
CellFormula
B1=EOMONTH(A1,0)-A1+1
 
Back
Top