Time format not working as expected

Hi All, Happy New Year.
It's been a long time since I was here. Glad your still here.
I have a sheet (sample attached) where two columns C & P are formatted as time hh:mm AM/PM
I want column Q to calculate the time difference between these two columns.
Q is formatted to give hrs and mins
I used the simple formula =C2-P2 but I'm getting the #Value! error.
Also where all the cells a formatted the same the typed results are different.
Typing the time in the cells in the same manner gives different results in different cells
I'm sure its something small I'm doing wrong so I'm hoping you can help.
Thanks.

The values in column C are not proper values of time - they are text values which just look like a time (hint am should show as AM). So, when you try to carry out some arithmetic using these values then you get an error message. All of the values in column P (except for P2) are text values as well. P2 shows 7:12 AM, but if you select that cell and look in the formula bar you will see 08/01/1900 07:12:00, so it is clearly only showing the time part of a date and time value.

Where did the data come from? It is important to rectify these variations as early as possible - hopefully at the source.

Hope this helps.

Pete

Hi Pete,
Thank you for your insight.
These are manually entered info.
What I'm trying to do is get the cells in columns C & P to operate as time columns so I can show the time difference between them.
Both are formatted (on my sheet) as custom hh:mm am/pm and what I've dicovered is when I thought the some cells was recognising a time value it was simply predicting/copying what was entered in other cells.
So what can I do to get the result I'd like.

Regards

You should enter times with colons between the hours, minutes and seconds parts, like this:

7:12:0

for 12 minutes past 7, and Excel will then interpret that as a time. If you do not have any hours you should use a zero, like 0:2:30 for 2 minutes and 30 seconds, otherwise Excel will take the minutes and seconds as hours and minutes.

You can apply whatever format you wish to those values.

Hope this helps.

Pete

I believe I may be able to help with this. I am working on creating a sheet that will take entered clock in time and entered clock out time and give the difference in minutes. My clock in time column and my clock out time column are both formatted as TIME, and I chose 1:30 PM and the next column that shows the difference is formatted as CUSTOM and I chose Custom h:mm. Then my difference formula is =B2-A2 - Just replace my columns for yours. Make sure you are putting a space before the AM or PM. I haven't quite figured out how not to have to do that just yet. But I'm looking. Also, I know this may be really simple for some, but I was getting an error simply because my answer column wasn't wide enough. So, once I stretched out my little excel box to make it wider, the answer was showing correctly. I hope this helps. I'm new here and just learning excel. Wish me luck

candidawalker75 - in regards to your comment about putting a space before the AM or PM, just a thought if you are open to using 24 hour time: set up your time display as time type 1:30 PM. Input 17:37 and it will display as 5:37 PM.