Hi all,
It looks like mont(december) doesn’t return any value.
Is that a bug or am I missing something?
Thanks!
Hi all,
It looks like mont(december) doesn’t return any value.
Is that a bug or am I missing something?
Thanks!
Strange. Is that date Glide formatted or are you using your own format?
I notice that the string ‘dec’ is using an extended ASCII character (é
).
Perhaps that is tripping Glide up.
Try replacing that with a standard ASCII ‘e’, and see if that helps.
Strange. Is that date Glide formatted or are you using your own format?
@ThinhDinh Yes, the date comes from Glide. However the spreadsheet is configured in french (so I can use €) which may change the way Glide records the dates.
Try replacing that with a standard ASCII ‘e’, and see if that helps.
@Darren_Murphy Thanks, will try. I believe I’ll need to change the spreadsheet local then, otherwise the character will change again. Not a great workaround since It will also change my default monetary unit to $.
As long as your column is a numeric column, you can always assign the unit (Euros) in the glide data editor, instead of doing it in the sheet.
I changed the local and it works now.
Thanks for your help.
If you don’t want to change your sheet locale setting, what you could do is insert a column in your sheet (say “Clean Date”), and then use a formula to replace the accent. Something like this:
=REGEXREPLACE(L2,"déc","dec")
(that assumes that December is the only month affected, and you’d probably need to make it an array formula)
Then in the data editor, you could use an if-then-else column to determine whether to use the raw date or the “clean” date in your math calculation…
There is probably a much more elegant way to do it, but that’s one way that could work.
Edit: Actually, I just did a little more testing and I suspect that REGEX replacement might not work in your case, because REGEXREPLACE expects strings, and your dates may be stored in the GSheet as numbers. But let me know if you want to pursue this option, and I’ll see if I can come up with a more robust formula.
Edit 2:
This might work:
=ARRAYFORMULA(IF(REGEXMATCH(TO_TEXT(L2:L),"déc"),REGEXREPLACE(TO_TEXT(L2:L),"déc","dec"),L2:L))
Does a template column work in this case?
Actually, yes!
But I think you need two template columns:
TPDec: just populates each row with ‘dec’
TPDate: substitutes déc with TPDec (dec)
And then Month uses TPDate in the math calculation.
I knew there would be a much simpler method
Thanks for the research guys!
I’ll have a look at templates. I haven’t tried it yet.
No problem.
I think that template solution is the simplest and best. And it’s easily extendable. For example, if you had another case, maybe “fèb”, then all you would need to do is add a TPFeb column and then include that as a second substitution in the TPDate template column.
It would be much easier and cleaner if we can replace by a custom value though. It always annoys me to have to create more columns to do this.
Being able to use regular expressions in templates would be awesome