PDA

View Full Version : Calculate days and cost



brett.white1
2016-10-28, 01:46 PM
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!

AliGW
2016-10-28, 02:02 PM
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.

brett.white1
2016-10-28, 03:12 PM
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)

p45cal
2016-10-28, 08:09 PM
A
B


1
02-Feb-16
28


2
26-Feb-16
4


3
29-Feb-16
1


4
26-Mar-16
6


5
26-Apr-16
5





Spreadsheet Formulas




Cell
Formula


B1
=EOMONTH(A1,0)-A1+1