Trouble with Dates in the future !!

[timsaddler]

When i force a date using the Addmonths function the data doesnt join as the Function seems to return decimal places.

Example

Addmonths(Date(MonthEnd(today())+1),0)  seems to return 41699.999999988

To join to all my other date i guess it needs to be an Integer

How do i acheive this?

[alexandros17]

write

Date(Addmonths(Date(MonthEnd(today())+1),0) )

[rbecher]

Strange, maybe better with Floor(Today()) ?

[Nicole-Smith · Accepted solution]

It's because monthend() returns the last date and the last possible time of the month:

MonthEnd(date [, shift = 0])

Returns a value corresponding to a timestamp with the last millisecond of the last date of the month containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the month which contains date. Negative values in shift indicate preceding months and positive values indicate succeeding months.

Examples:

monthend ( '2001-02-19' ) returns '2001-02-28' with an underlying numeric value corresponding to '2001-02-28 23:59:59.999'

monthend ( '2001-02-19', -1 ) returns '2001-01-31' with an underlying numeric value corresponding to '2001-01-31 23:59:59.999'

You need to use floor() like Ralf Becher suggested, but it needs to be around the monthend() function instead of the today() function:

Addmonths(Date(Floor(MonthEnd(today()))+1),0)

I'm also not sure why you're using the AddMonths() function if you're not adding or subtracting any months...

[jaimeaguilar]

Just adding to what's already been said, I think the problem is the order of the functions, you typed this:

Addmonths(Date(MonthEnd(today())+1),0)

The thing here is that Date would be placed first, because no matter what tranformations you make to today(), at the end you want to get a date. Also is not the same to add a month and then getting the end of the month than getting the end of the month and then adding a month:

today() = february 14 -> date(MonthEnd(addmonths(today(), 1)) will return march 31st (31/03/2014 asuming date is set to DD/MM/YYYY)

today() = february 14 -> date(Addmonths(MonthEnd(Today()), 1)) will return march 28th (28/02/2014 assuming date is set to DD/MM/YYYY)

Regards

[rbecher]

I'm still not sure what you're trying to get here. Perhaps the beginning of the next month?

=MonthStart(AddMonths(Today(),1))