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

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

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

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

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

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