I have an issue with usersettings. I want to...

Christoph_Bucke · 02-22-2018 01:09 PM

I have an issue with usersettings.

I want to have a setting of a specific date - always the last october, 1st .

I detected that year(today()) returns the year as number with thousand seperator and there is no possibility to inhibit that. So i used a formula in initial value as follows: =IF ( MONTH(TODAY())>9 , “10/01/” & SUBSTITUTE(TEXT(YEAR(Today())),",","") , “10/01/” & SUBSTITUTE(TEXT(YEAR(Today())-1),",",""))

But the seperator is different on different devices. Comma and dot are possible. This means my formula does not work.

Any idea on that?

Aleksi_Alkio

You can use LEFT and RIGHT expressions like… CONCATENATE(LEFT(TEXT(YEAR(TODAY())),1),RIGHT(TEXT(YEAR(TODAY())),3))

You can read the number much easier without the thousand separator if you use CONCATENATE instead of TEXT expression. Typing like CONCATENATE(YEAR(TODAY())) will give you result as 2018.

Aleksi_Alkio

You can use LEFT and RIGHT expressions like… CONCATENATE(LEFT(TEXT(YEAR(TODAY())),1),RIGHT(TEXT(YEAR(TODAY())),3))

You can read the number much easier without the thousand separator if you use CONCATENATE instead of TEXT expression. Typing like CONCATENATE(YEAR(TODAY())) will give you result as 2018.

Christoph_Bucke

I tried LEFT and RIGHT before with totally crazy results. Thats why I changed to substitute. Your tip with CONCATENATE(YEAR(TODAY())) seems to work well. I wonder what is the difference to using &. Anyway, thanks a lot

Aleksi_Alkio

TEXT expression is meant to read also the format and that’s why your year is written like 2 018. CONCATENATE dosn’t read the format, just the pure data.