Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference between last date of month and given date
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference between last date of month and given date
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 Replace @mailinator.com with @tiscali.co.uk "Nikhil" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference between last date of month and given date
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difference between last date of month and given date
On Fri, 13 Jun 2008 02:42:02 -0700, Nikhil
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates - Need to display date one month prior to user-entered date | Excel Worksheet Functions | |||
Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
=VLOOKUP(1,Nationality!B5:B29,IF(MONTH(date)6,MONTH(date)-6, MON | Excel Worksheet Functions | |||
Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) |