Home |
Search |
Today's Posts |
#1
|
|||
|
|||
inputing different days in a month for a formula
I was wondering if it is possible to recognize the different numbers of
months in a day without using additional columns. Basically I want to calculate the cost per day per month and per year. If anyone has any information on this, please let me know. Thanks |
#2
|
|||
|
|||
At my time of life, I sometimes wish there were many months in a day
!!! However, one way to find the number of days in a month would be =DAY(EOMONTH(A1,0)) where A1 holds the date. You need to have the Analysis Toolpak loaded - ToolsAddins and check the Analysis Toolpak box -- Regards Roger Govier "I need to sort the items by their item" oft.com wrote in message ... I was wondering if it is possible to recognize the different numbers of months in a day without using additional columns. Basically I want to calculate the cost per day per month and per year. If anyone has any information on this, please let me know. Thanks |
#3
|
|||
|
|||
I should have added, format the cell with the formula as General
-- Regards Roger Govier "Roger Govier" wrote in message news:... At my time of life, I sometimes wish there were many months in a day !!! However, one way to find the number of days in a month would be =DAY(EOMONTH(A1,0)) where A1 holds the date. You need to have the Analysis Toolpak loaded - ToolsAddins and check the Analysis Toolpak box -- Regards Roger Govier "I need to sort the items by their item" oft.com wrote in message ... I was wondering if it is possible to recognize the different numbers of months in a day without using additional columns. Basically I want to calculate the cost per day per month and per year. If anyone has any information on this, please let me know. Thanks |
#4
|
|||
|
|||
To get the number of days in a month, with any date of the target month in
A1 use: =DAY((A1+32-DAY(A1+32))) so to use this in a calculation - say the daily rate is in C1 the total for the month will be: =DAY((A1+32-DAY(A1+32)))*C1 -- HTH Sandy Replace@mailinator with @tiscali.co.uk "I need to sort the items by their item" oft.com wrote in message ... I was wondering if it is possible to recognize the different numbers of months in a day without using additional columns. Basically I want to calculate the cost per day per month and per year. If anyone has any information on this, please let me know. Thanks |
#5
|
|||
|
|||
Yes Roger that did help for the months. Now how do I go about creating a
formula for the year. "Roger Govier" wrote: At my time of life, I sometimes wish there were many months in a day !!! However, one way to find the number of days in a month would be =DAY(EOMONTH(A1,0)) where A1 holds the date. You need to have the Analysis Toolpak loaded - ToolsAddins and check the Analysis Toolpak box -- Regards Roger Govier "I need to sort the items by their item" oft.com wrote in message ... I was wondering if it is possible to recognize the different numbers of months in a day without using additional columns. Basically I want to calculate the cost per day per month and per year. If anyone has any information on this, please let me know. Thanks |
#6
|
|||
|
|||
Just take the 2 dates away from each other e.g.
A1 26/08/05 B1 26/08/05 =A1-B1 result 365 Ensure the cell with the formula is formatted General, other wise the result will show up as a date 30/12/1900 Or, if you are wanting a single formula based upon the source cell as per the previous monthly calculation then with date in A1 =DATE(YEAR(A1)+1,1,0)-DATE(YEAR(A1),1,0) This relies upon the fact that Day 0 of a month, is equal to the last day of the previous month so with a date of 26/08/2005 in A1, the formula is giving DATE Year A1 = 2005 + 1 = 2006, Month 1, Day 0 therefore 31 /12/2005 DATE Year A1 =2005 , Month 1, Day 0 Therfore 31/12/2004 One minus the other = 365 probably, more strictly it should be =DATE(YEAR(A1)+1,1,0)-DATE(YEAR(A1),12,31) + 1 as we know that the last month of the year has 31 days, but we would need to add a 1 to the result to make the total inclusive of the first and last days of the year. -- Regards Roger Govier "I need to sort the items by their item" oft.com wrote in message ... Yes Roger that did help for the months. Now how do I go about creating a formula for the year. "Roger Govier" wrote: At my time of life, I sometimes wish there were many months in a day !!! However, one way to find the number of days in a month would be =DAY(EOMONTH(A1,0)) where A1 holds the date. You need to have the Analysis Toolpak loaded - ToolsAddins and check the Analysis Toolpak box -- Regards Roger Govier "I need to sort the items by their item" oft.com wrote in message ... I was wondering if it is possible to recognize the different numbers of months in a day without using additional columns. Basically I want to calculate the cost per day per month and per year. If anyone has any information on this, please let me know. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for Extracting Month out of a Date column | Excel Discussion (Misc queries) | |||
number of days in a month | Excel Worksheet Functions | |||
inputing formula | Excel Worksheet Functions | |||
excel formula that can subtract days | Excel Worksheet Functions | |||
Formula to convert to month | Excel Worksheet Functions |