inputing different days in a month for a formula

[Guest]

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

 

[Roger Govier]

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 - Tools>Addins and check the
Analysis Toolpak box

--
Regards

Roger Govier


"I need to sort the items by their item"

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk


"I need to sort the items by their item"

 

[Guest]

Yes Roger that did help for the months. Now how do I go about creating a
formula for the year.

 

[Roger Govier]

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"