Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

FUNCTION to calculate START of Month

0 views
Skip to first unread message

RoyB

unread,
Sep 9, 2003, 12:00:23 PM9/9/03
to
I've been trying to work with the function EOMONTH to give
me the beginning of the FOLLOWING month.

My date starts on 12/09/2003, I want to calculate for a
list of dates, here returning the correct value
01/10/2003.

Anyone got any ideas?

Thanks

Peo Sjoblom

unread,
Sep 9, 2003, 12:08:02 PM9/9/03
to
Date in A1

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

the true beginning of next moth should be

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

--

Regards,

Peo Sjoblom


"RoyB" <royb...@hotmail.com> wrote in message
news:086001c376eb$79f18790$a001...@phx.gbl...

RoyB

unread,
Sep 9, 2003, 12:49:43 PM9/9/03
to
Thanks Peo

After I add a day to the end of the function you sent, I
can get the calendar start day for the next month, you
made my life easier today.

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

>.
>

Daniel.M

unread,
Sep 9, 2003, 3:17:30 PM9/9/03
to
> =DATE(YEAR(A1),MONTH(A1)+1,)+1

AFAIK, if you want the first day of next month, this one is clearer:

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

Regards,

Daniel M.

GB

unread,
Sep 9, 2003, 8:23:53 PM9/9/03
to
RoyB

I am curious. Why does the following not work for you?

= Eomonth(a1,0) +1

Do you get #Name? as the result?

Or do you get a number like 37895?

Geoff


"RoyB" <royb...@hotmail.com> wrote in message
news:086001c376eb$79f18790$a001...@phx.gbl...

RoyB

unread,
Sep 10, 2003, 6:00:49 AM9/10/03
to
Geoff

I get a #NAME error, and if that function was successful,
I think the +1 in that position gives a date that is one
month ahead, not the beginning of the next month, which is
what I need.

>.
>

GB

unread,
Sep 10, 2003, 6:14:04 AM9/10/03
to
The reason for the error is that you need to install the analysis tool pack.
Look up eomonth in help as it says how to do that.

The +1 adds one day to the date calculated by the eomonth function. It
definitely gives you the first of the following month!

Regards

Geoff

"RoyB" <royb...@hotmail.com> wrote in message

news:0a3601c37782$6987b0e0$a401...@phx.gbl...

David McRitchie

unread,
Sep 10, 2003, 7:08:50 AM9/10/03
to
Hi Roy,
Since a date could be in any order i.e. dd/mm/yy, mm/dd/yy, yy/mm/dd
or dd/mm/yyyy, mm/dd/yyyy, yyyy-mm-dd, yyyy/mm/dd
I would suggest giving format of the date, or at least to avoid
confusion between month and day of month to choose a
day of month greater than 12 in at least one date.

Without the Analysis Tool Pack you can use
=Date(YEAR(A1), MONTH(A1)+1, 1)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"GB" <ThisIsNotMy...@Anywhere.InTheUniverse> wrote in message news:3f5ef950$1...@news1.homechoice.co.uk...

0 new messages