Currency format does not always work using a form

Carlyn F

We have a form to populate an expenses smartsheet. The expense column in the smartsheet is formatted as currency, and most of the time when a form is submitted, the expense comes through to the smartsheet as currency. Every once in a while, it is not formatted as currency. I cannot figure out why this happens. Ideas? Any way to default a form as currency?

Mike TV

@Carlyn F

Can you show the column here with examples of it both working and not working? Probably don't need to see all of the columns, just the currency one.

Also, have you contacted the person who submitted the ones that aren't displaying properly to see if they manually typed it in or if they copy-pasted it out of something else?

What happens if you drop in a new column and use an ISTEXT or ISNUMBER function referring to that cell with the format problems? Maybe its coming across as text and not as a number?

Carlyn F

Thank you for all your ideas, I am attaching a picture of the currency column with examples of both working and not working numbers. FYI, all of these expenses were input by me using a form, the $250 amount I copied and pasted but it worked properly. The other amounts I input manually. I also added an ISNUMBER checkbox column before inputting these expenses, and all of them were checked off as numbers (I also included that column). That one particular amount (100.13) is the one that did not format correctly every time, but I can't fathom how the amount itself would be causing that to happen. Any ideas with this additional info?

Carlyn F

Hey MikeTV, I just realized that the amounts that didn't format as currency are amounts with pennies. Any idea why that would be the case? Again the amounts are input using a form.

Carlyn F

@Mike TV

Did you see my followup comments? I didn't tag you last time, sorry. I sent a screenshot of how it looks and then realized that the currency format does not work when there are pennies involved. Any ideas????

Mike TV

@Carlyn F

Hi Carlyn. Sorry, I didn't see your responses until this morning. I looked into this issue and you're not the only one having it. I found a few posts mentioning this problem but didn't really see any solutions to it. However, I did figure out a solution for you this morning.

So the column you use to gather the $ amount via a form will be your "helper" column. You can actually hide it on your sheet. You'll want to create another new column and format it as $ currency. Then give it a formula such as:

=[Input Amount Expense]@row

It basically takes the value of 100.13 and puts it in the new column. Whatever problem SmartSheet is having formatting any number with cents from a form, the new column won't have a problem formatting it correctly. Example:

Lisa Shields

I'm having this problem too. Thanks @Mike TV for the suggested workaround.

Genevieve P.

Hi all,

Jumping in here to let you know that Forms received a number of updates this week - one of which is that entries with decimals will retain the currency formatting in your sheet!

Here's more information: NEW! Forms minor updates and enhancements

Cheers,

Genevieve