Difference between last date of month and given date

[Nikhil]

I have a date entered in a cell...15-Apr-08. I want to calculate the no. of
days between 30-Apr-08 and 15-Apr-08. In this case 15 days.

Secondly..i have to pay a monthly sum of say 5000 per month....

i want to calculate the total amount from start date till the 30(31st) of
the previous month

how can i do that....

e.g on 15th Jun, i want to calculate the total amount between 15th Apr. to
31st May.

The amount should work out to (2500+5000) = 7500

Plz help

 

[Sandy Mann]

I don't really follow what it is that you are trying to do but in answer to
your first question try:

=DATE(YEAR(A1),MONTH(A1)+1,0)-A1

and format the cell as General or Number

assuming that you want the proportion of the remainder of the month rounded
to one decimal place, (so that the 15th of May will still equate to 0.5 of a
month) then try:

=ROUND((DATE(YEAR(A1),MONTH(A1)+1,0)-A1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0)),1)*5000+5000

If you don't want it rounded, (so that the 15th of May would equate to
0.516129032 of the month remaining), then use:

=(DATE(YEAR(A1),MONTH(A1)+1,0)-A1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0))*5000+5000

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Jarek Kujawa]

that is not the smartest way but try to put 15-Apr-08 in A1 and use
the formula:

=2500+5000*(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),0))-MONTH(A1))

cannot think now of anything better

HIH

 

[Ron Rosenfeld]

I have a date entered in a cell...15-Apr-08. I want to calculate the no. of
days between 30-Apr-08 and 15-Apr-08. In this case 15 days.

Secondly..i have to pay a monthly sum of say 5000 per month....

i want to calculate the total amount from start date till the 30(31st) of
the previous month

how can i do that....

e.g on 15th Jun, i want to calculate the total amount between 15th Apr. to
31st May.

The amount should work out to (2500+5000) = 7500

Plz help

Note that the last day of the preceding month can be given by the formula:

=A1-DAY(A1)

So:
Days from StartDate to End of Month of the month of the startdate:

=32-DAY(StartDate)-DAY(StartDate-DAY(StartDate)+32)

This does NOT include StartDate. e.g. if StartDate = 1 Apr, the formula will
return 30-1 = 29. If you want to include StartDate in the count, add 1:

=33-DAY(StartDate)-DAY(StartDate-DAY(StartDate)+32)

For the number of days from StartDate to the end of the month prior to some
other date:

=SomeOtherDate - DAY(SomeOtherDate) - StartDate

Again, if you need to include StartDate in the count, add 1.

You will need to give more information on how you are calculating the "total
amount" for an accurate result, especially considering that months can vary in
length from 28-31 days.
--ron