Batch name formatting incorrectly

Mike TV

I've got an odd thing happening on one of our sheets. We do production and so we have batch names for production runs. Our batch names consist of a date, then a 3 digit code, then a suffix. So the sheet pastes together a few things entered by the production team to create the batch name.

The way it happens is the production enters a date of production onto the sheet. We're in the US so the date is formatted on the sheet as MM/DD/YY. Then a helper formula does this to strip the first / off of it:

=REPLACE([Batch Date]@row, 3, 1, "")

Then another formula to strip the 2nd / off of the date:

=REPLACE([Batch date Helper 1]@row, 5, 1, "")

Finally we have a formula that pieces together the date with a strain code (the 3 digit code I mentioned) and a suffix:

=[Batch date helper 2]@row + [Strain Code 1]@row + "-" + INDEX({Batch Names Range 1}, MATCH([Production Type]@row, {Batch Names Range 2}, 0))

Here's the problem I keep seeing. Someone will submit a production batch which sends me an email. I load up the Dynamic View I use to interact with the batch but for some reason the batch name ends up with a European date formatting. For example, the batch that was processed yesterday ended up with a batch name beginning with 080922 instead of 090822.

When this happens, if I load up the actual sheet (instead of the Dynamic View) it will automatically correct the batch name format. So today it automatically changed from 080922 to 090822.

Any ideas why this is happening?

@Andrée Starå

@Paul Newcome

Tomasz Giba

@Mike TV

"Cosmetic" change ^^:

="" + IF(LEN(MONTH(Date@row)) = 1, "0" + MONTH(Date@row), MONTH(Date@row)) + IF(LEN(DAY(Date@row)) = 1, "0" + DAY(Date@row), DAY(Date@row)) + SUBSTITUTE(YEAR(Date@row), "20", "")

Paul Newcome

This would be my personal preference...

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

Paul Newcome

I would use DAY, MONTH, and YEAR functions in the specific order you want them in.

Mike TV

@Paul Newcome

Good idea. My problem with that is this:

=MONTH(Date@row) + "" + DAY(Date@row) + SUBSTITUTE(YEAR(Date@row), "20", "")

With this formula, 01/02/2022 becomes 1222 instead of 010222. Any ideas on how to fix?

Tomasz Giba

Hi @Mike TV

I'm not sure if I'm allowed to answer as Paul was mentioned but you may check this:

=IF(LEN(MONTH(Date@row))=1,"0" + MONTH(Date@row),MONTH(Date@row)) + IF(LEN(DAY(Date@row))=1,"0" + DAY(Date@row),DAY(Date@row)) + SUBSTITUTE(YEAR(Date@row), "20", "")+""

Mike TV

@Tomasz Giba

Any help is much appreciated. Your formula worked for the 01/02/22 date but for 11/12/22 it translated to 2322.

Tomasz Giba

@Mike TV

"Cosmetic" change ^^:

="" + IF(LEN(MONTH(Date@row)) = 1, "0" + MONTH(Date@row), MONTH(Date@row)) + IF(LEN(DAY(Date@row)) = 1, "0" + DAY(Date@row), DAY(Date@row)) + SUBSTITUTE(YEAR(Date@row), "20", "")

Paul Newcome

This would be my personal preference...

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

Mike TV

@Tomasz Giba

@Paul Newcome

Thanks guys for the help. Both worked.

Paul Newcome

Happy to help. 👍️