Calculate days in each month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PVS - 27 Mar 2008 05:40 GMT

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.

Signature

Thanks for any help offered.

PVS

Reply to this Message

OssieMac - 27 Mar 2008 06:05 GMT

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.

Signature

Regards,

OssieMac

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

Reply to this Message

Tyro - 27 Mar 2008 06:07 GMT

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

Tyro

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

Reply to this Message

Ivyleaf - 27 Mar 2008 08:04 GMT

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.

> If your date is in A1 and you have the Analysis Tookpak installed then
> =DAY(EOMONTH(A1,0))
[quoted text clipped - 13 lines]
>
> - Show quoted text -

Reply to this Message

Ivyleaf - 27 Mar 2008 08:07 GMT

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,

> If you don't have Analysis Toolpak installed (safer option) this
> should do it for you, assuming your date is in A1:
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Reply to this Message

Bob Phillips - 27 Mar 2008 09:56 GMT

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

Signature

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

> If you don't have Analysis Toolpak installed (safer option) this
> should do it for you, assuming your date is in A1:
[quoted text clipped - 33 lines]
>
> - Show quoted text -

Reply to this Message

David Biddulph - 27 Mar 2008 09:07 GMT

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.

On Mar 27, 4:07 pm, "Tyro" <T...@hotmail.com> wrote:

> If your date is in A1 and you have the Analysis Tookpak installed then
> =DAY(EOMONTH(A1,0))
[quoted text clipped - 20 lines]
>
> - Show quoted text -

Reply to this Message

Ivyleaf - 27 Mar 2008 09:34 GMT

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.

On Mar 27, 7:07 pm, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:

> Or =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> --
[quoted text clipped - 36 lines]
>
> - Show quoted text -

Reply to this Message

Ron Rosenfeld - 27 Mar 2008 12:22 GMT

>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

Reply to this Message