Excel & Forms - Automatically Add Space between Time & AM/PM

Question

Hello,

I have a Microsoft Form setup.  Since Forms does not provide a simple time field, I have a text field.

As a side note, I also have a SharePoint Site setup with a direct link to the corresponding spreadsheet which discloses all of the responses to the form.

Now despite indicating the specific format that the time should be typed as, in the event the an individual enters "12:00PM" instead of "12:00 PM", is there a way for the Excel spreadsheet to automatically add this space between the time and the AM/PM.  If the space is not present, this affects the functionality of the formulas I created that are associated with this times.

Please let me know if I need to clarify or provide additional information.

Thanks!

Answer 1

try

=VALUE(IF(ISNUMBER(SEARCH(" ",A1)),A1,IF(ISNUMBER(SEARCH("AM",A1)),SUBSTITUTE(UPPER(A1),"AM"," AM"),SUBSTITUTE(UPPER(A1),"PM"," PM"))))

Answer 2

Good Morning,

Any solution to address the time value being shown as a decimal? (noted above)

Thanks,

Answer 3

Good Afternoon,

This solution would work.  However, if the time was input correctly (with a space - "12:00 PM") the formula changes the time value to a decimal instead of repeating the identical time.

Answer 4

Hi ChristopherToth1，

According to your description, we got your concern about two issues.

One is user input formats are not uniform. The other is you want to the Excel automatically add the space between the time and the AM/PM automatically. If we misunderstand, please correct us.

So, first we suggest you notify users the format you want in the form to reduce the error format.

And in spreadsheet, please try use the formula to meet your needs. The following step may help you.

1.Open the spreadsheet and type 12:00PM.

2. Select the cell B1.
3. Enter the formula in the formula bar: =IF(ISNUMBER(SEARCH(" ",A1)),A1,IF(ISNUMBER(SEARCH("AM",A1)),SUBSTITUTE(A1,"AM"," AM"),SUBSTITUTE(A1,"PM"," PM")))

I hope this information is useful to you. Have a good day.

Best regards

Tin

Answer 5

This is not an answer that totally removes the problem. Only how to rectify the entries.

Suggest that you make a backup of your workbook before testing the following in case it does not do what is expected.

Not using a space between the numeric and the AM or PM causes the entry to become a text entry instead of a recognized time. This is a problem that has been around for ever.

If the times are all in a specific column you can usually select the column and using Replace insert PM in the "Find what" field and space and PM in the "Replace with field". Repeat for AM.

One would think that this method would change times that had been correctly entered with the one space to 2 spaces but fortunately it does not. Even if you enter a time with multiple spaces, Excel reduces the multiple spaces to one space.