Calculate days in each month

P

PVS

Exel 2002. Vista Home Premium. Is there a function that will calculate the
total number of days in each month. I need to average totals against the
number of days in each month. For example, $40,000 / number of days in the
month. I would like to make it a formula so I can copy it rather than have to
manually enter the number of days in each month.
 
O

OssieMac

These examples might help you.

=DAY(EOMONTH(TODAY(),0)) Returns 31 which is the last day of current month

=DAY(EOMONTH("2 feb 2008",0)) Returns 29 (Might need "Feb 2 2008"
depending on your system date format)

=DAY(EOMONTH("2/2/2008",0)) Returns 29

=DAY(EOMONTH(D5,0)) Use where D5 is an actual date.

Note the Zero parameter is current month. Use 1 for last day of next month ,
2 for month after that etc.
 
T

Tyro

If your date is in A1 and you have the Analysis Tookpak installed then
=DAY(EOMONTH(A1,0))

Tyro
 
I

Ivyleaf

If you don't have Analysis Toolpak installed (safer option) this
should do it for you, assuming your date is in A1:

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

Cheers,
Ivan.
 
I

Ivyleaf

Note that Excel will probably try to display the result of this
formula as a date, in which case just reformat the cell as 'General'.

Cheers,
 
D

David Biddulph

Or =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
--
David Biddulph

If you don't have Analysis Toolpak installed (safer option) this
should do it for you, assuming your date is in A1:

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

Cheers,
Ivan.
 
I

Ivyleaf

Nice one David. I knew there should be one step simpler. I hate
relying on addins... nice to see something just as easy without them.

Ivan.
 
B

Bob Phillips

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

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Note that Excel will probably try to display the result of this
formula as a date, in which case just reformat the cell as 'General'.

Cheers,
 
R

Ron Rosenfeld

Exel 2002. Vista Home Premium. Is there a function that will calculate the
total number of days in each month. I need to average totals against the
number of days in each month. For example, $40,000 / number of days in the
month. I would like to make it a formula so I can copy it rather than have to
manually enter the number of days in each month.

I think this may be the shortest, so far:

=32-DAY(A1-DAY(A1)+32)
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top