Calculate days in each month

[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.

 

[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.

 

[Tyro]

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

Tyro

 

[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.

 

[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,

 

[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.

 

[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.

 

[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,

 

[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