FUNCTION to calculate START of Month

[RoyB]

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]

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

 

[RoyB]

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

 

[GB]

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]

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.

 

[David McRitchie]

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)