Formula issus while automation

Raphael Lng

Hi All,

I am currently having an issue for one o my workflow, I will try to describe briefly:

I have a ticket system that we use for one customer. Whenever a ticket is completed in the "Open ticket sheet" by our Team, the line is moved to an "archive sheet" thanks to an automation.

In the open ticket sheet, I have this formula:

I use then this value month/year to create my reports and dashboard.

But in my archive sheet, it looks like this formula is acting randomly after the automation:

I am sure it is not my users who are changing value as the column is locked in both sheets.

I didn't want to make the formula in the archive sheet because then when I am looking my dashboard it's not up to date, I always need to open the archive sheet to refresh the dashboard with proper datas.

Could someone help me with this issue or propose me another formula which could work ?

thanks a lot,

Raphael

Paul Newcome

@Dale Murphy I'd say your first one would be on the right track for mm/yy (after we adjust the syntax a bit we can add on to it).

=IF(MONTH(Datum)@row)<10, "0", "") + MONTH(Datum)@row) + "/" + RIGHT(YEAR(Datum@row), 2)

Dale Murphy

@Raphael Lng (Pardon me, this will be in English)

Instead of using the RIGHT function on a date cell, I would recommend Year() and Month(). You can then stitch them together if that is the desired final outcome:

=MONTH(Datum)@row + "/" = YEAR(Datum@row) - however, that gives you a four digit year (and often a one digit month.

So you could use:

=MID(Datum@row, 4, 2) + "/" + RIGHT(Datum@row, 2)

Cheers,

dm

Paul Newcome

@Dale Murphy I'd say your first one would be on the right track for mm/yy (after we adjust the syntax a bit we can add on to it).

=IF(MONTH(Datum)@row)<10, "0", "") + MONTH(Datum)@row) + "/" + RIGHT(YEAR(Datum@row), 2)

Raphael Lng

Hi Both,

Thank you very much, I gave a try to the latest formula from Paul and it works perfectly now ! and I keep the datas on my Dashboard as it was

Have a nice day,

Raphael

Paul Newcome

Happy to help. 👍️