Top-loading form data does not take on currency or decimal place formatting in sheet

Stacey C

In many of my forms, I select the option to have new rows added to the top so they are easy to find. When I do this, however, I find that most of the time, the data does not keep the numerical formatting I've applied to the column previously. This makes automated purchase order approvals come through without the $, and when it defaults to tenths instead of hundredths it causes problems for my payroll team who have to pay to the 1/4 hour. How can I ensure new data takes on the format I want? Interesting point - the color format is kept, but not numerical format.

In the example below, the Total Cost field is a calculated field from the columns on the right (which are pulled in from a separate sheet using Index/Match function). As you can see, my newest entries from the form are not showing currency format.

Similarly, when my late call hours come in from supervisors, I only see one decimal place, but the payroll must calculate to .25 or .75 of an hour so I need to capture that for their export.

Any help is appreciated as this is only 2 of many examples. ~Stacey

Stacey C

I may have found a work around for this issue @Eric Law! Not a perfect solution, but by wrapping my formula that calculates cost in a VALUE() function, my currency column formats seem to be holding up with new data. I had to create a new field for the decimal issue since that value is directly from form input, not calculated, but =VALUE(1*[Form Entry]) solved that decimal problem as well. Seems excessive, but it's working so far. Thanks for your comments!

Eric Law

Hello @Stacey C have you used the number format in the top right?

If you apply the money to the column it should format it as $xxxx.xx by default. In addition, you can choose the decimal amount too. Just brainstorming

If this doesn't work, how does the data get inputted? Via form?

Stacey C

Thanks for writing back, @Eric Law! Yes, I have highlighted the column and selected $ and .00 for my respective columns, but the formatting will not hold on a sheet where the new form entries are submitted to the top of my list. There are very few forms that I have load to bottom of the list, but I haven't noticed this same issue on those particular forms.

If you hear of any other way to fix this issue, I would appreciate your input! Have a great day.

Eric Law

@Stacey C I see what you mean... That is annoying. So you will need to apply the $ format or the hundredths format before exporting every time. Formulas don't even seem to work if you always need the hundredth decimal point... I am stumped as well. Sorry.

Stacey C

I may have found a work around for this issue @Eric Law! Not a perfect solution, but by wrapping my formula that calculates cost in a VALUE() function, my currency column formats seem to be holding up with new data. I had to create a new field for the decimal issue since that value is directly from form input, not calculated, but =VALUE(1*[Form Entry]) solved that decimal problem as well. Seems excessive, but it's working so far. Thanks for your comments!