Get the today date value in next year

Gunasuriya_Ravi · 06-30-2020 12:31 AM

I am having two fields in my table.

Date of review and Next year review

Date of review : I will get the today date by the expression TODAY()

For the Next year review field i need the same day and month with next year has value ,can you help me with the expression

Suvrutt_Gurjar

I believe , handling date and time logic in general can be very challenging because it has many other dependencies such as your spreadsheet locale, device locale and even browser locale and AppSheet editor settings for the tables.

So in the following suggested expressions , you may need to make further adjustments if need be. I have of course tested them for a few critical cases such as 28, 29 Feb on leap and non leap year. For 29th Feb on a leap year the next year date will be set for 28th Feb

DD/MM/YYYY format expression.

Assumes input date, TODAY() is in DD/MM/YYYY format.

DATE(CONCATENATE(IF(AND(MONTH(TODAY())=2, DAY(TODAY())=29), DAY(TODAY())-1,DAY(TODAY())),"/",MONTH(TODAY()),"/", (YEAR(TODAY())+1)))

MM/DD/YYYY FORMAT expression
Assumes input date TODAY() IS IN MM/DD/YYYY format

DATE(CONCATENATE(MONTH(TODAY()),"/", IF(AND(MONTH(TODAY())=2, DAY(TODAY())=29), DAY(TODAY())-1,DAY(TODAY())),"/", (YEAR(TODAY())+1)))

Koichi_Tsuji

Clever.

Gunasuriya_Ravi

`hi suvrutt
I cant get the correct answer ,its giving different value

Suvrutt_Gurjar

Please share the column settings and expressions of [Next year review], [ Date of review] columns and screenshots of wrong value that you are getting. Imean for what input value what output value you are getting and in which view.

hru

I am using this for a year addition:

(EOMONTH(([Date] - DAY([Date])), 12) + Day([Date]))

Maybe it helps, I am happy with the result and tested several time.

Suvrutt_Gurjar

Nice, efficient alternative @Heru

Steve

This will give March 1 of next year if evaluated on Feb 29 of this year. Otherwise, I like it!

Koichi_Tsuji

What is your suggestion, master?

Suvrutt_Gurjar

We will await @steve’s guidance. But since leap year is followed by non leap year, Feb 29 converting to 1st March seems the right thing to do. So, @Heru’s expression is correct as per my understanding and very compact. He has made nice use of EOMONTH() function.

hru

Earlier, it was longer, like this:

EOMONTH([Date],12)-(DAY(EOMONTH([Date],12))-DAY([Date]))

Which having the same result, through trial and error (mostly error), I got that.

Will wait for Steve’s guidance.

LeventK

I believe below expression will take care of the leap years for Feb

IF(
	AND(
		MOD(YEAR([Date]),4)=0,
		MONTH([Date])=2,
		DAY([Date])=EOMONTH([Date],0)
	),
	(EOMONTH(([Date] - DAY([Date])), 12) + Day([Date]) - 1),
	(EOMONTH(([Date] - DAY([Date])), 12) + Day([Date]))
)

hru · But March 1 2021 is the correct result? (there is no 29 Feb next year)

But March 1 2021 is the correct result? (there is no 29 Feb next year)

Steve

March 1 isn’t necessarily the wrong result, but some app creators might expect the resulting date to be in the same month. Really just highlighting this peculiarity of date math.