Whenever I input a time in the Excel cell it is not recognized. Given the custom format for the cell(s) that I am using, a recognized value inputted into the formula bar will be displayed correctly as "5:00p". The custom cell format I am using is shown below:
If I put "5:00p" in the cell/formula bar it does not store the value correctly so that formulas in other cells can run correctly. In order for it to recognize the value it has to look like what is shown below:
How do I adjust the settings or translate the pasted text so that if I copy and paste "5:00p" into the cell it will display as "5:00p" and store the correct time value so other calculations can still run?
If you want to use a time with AM/PM then use this form:
The space between the value and the abbreviations (AM/PM) is essential.
If your input not corresponds to these standards then with a VBA code first convert it to a date/time, then apply the custom display format for the converted value.