Batch name formatting incorrectly

Mike TV
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

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!