Share via

PLEASE HELP!! How do I remove AM or PM from Times?

Anonymous
2021-09-09T17:23:35+00:00

I have created a spreadsheet for tracking exercise. If I go for a 10 minute run, I type in 10:00 but EXCEL views that 10:00 AM. If I take a 30 second rest, I type in 0:30 but EXCEL views that as 12:30AM. I'M NOT TRACKING TIME OF DAY, I'M TRACKING MINUTES AND SECONDS. I have tried entering "mm:ss" in custom format or "[mm]:ss". That allows it to view the time correctly. But in the formula bar, it still displays AM/PM next to the time. How do I get rid of that??

Also if I enter the format [mm]:ss and then I type in 6:00 which is 6 MINUTES AND 0 SECONDS. It appears as 360.00. Where in the world is it getting 360 from? How does 6 minutes and 0 seconds turn into 360.00? What is the problem here?

Thank you for any and all help.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-09-09T18:15:56+00:00

    If you enter a time such as 5:30, Excel interprets it as 5 hours and 30 minutes, not as 5 minutes and 30 seconds.

    You will have to enter 0:5:30 to make Excel interpret it as 5 minutes and 30 seconds.

    You can use a custom format to display the time as cumulative time in the cell, but Excel will always display a time according to your system time format in the formula bar. The system time format is for clock time, so if you use AM/PM for clock time, you'll see it in the formula bar. If you don't like that, switch to 24 hour clock time in Windows.

    2 people found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-09-10T21:16:46+00:00

    The number format only tells Excel how to display the value in the cell. For dates and times, it doesn't tell Excel how to interpret the value you enter.

    It would be nice if Excel took the date or time format into account, but sadly, it doesn't.

    0 comments
  3. Anonymous
    2021-09-10T18:25:41+00:00

    Thank you HansV,

    I'm a little confused though because the format I enter was mm:ss or [mm]:ss. That should tell it to enter the first number as minutes and the second number as seconds. But your telling me that the first number is interpreted as hours, second number as minutes, third number as seconds. Even though the format specifically says the first number is minutes and the second number is seconds. This is what I don't understand?

    So if I enter 2:00 (2 minutes, 0 seconds). Despite the format I entered, EXCEL would see it as 2 hours, 0 minutes, 0 seconds? So what's the point of using a format like mm:ss if the first number counts as something completely different?

    Alexander

    0 comments
  4. Anonymous
    2021-09-10T18:24:32+00:00

    Thank you Jeovany,

    I'm a little confused though because the format I enter was mm:ss or [mm]:ss. That should tell it to enter the first number as minutes and the second number as seconds. But your telling me that the first number is interpreted as hours, second number as minutes, third number as seconds. Even though the format specifically says the first number is minutes and the second number is seconds. This is what I don't understand?

    So if I enter 2:00 (2 minutes, 0 seconds). Despite the format I entered, EXCEL would see it as 2 hours, 0 minutes, 0 seconds? So what's the point of using a format like mm:ss if the first number counts as something completely different?

    Alexander

    0 comments
  5. Anonymous
    2021-09-09T18:18:28+00:00

    Hi

    To answer the question " ...Where in the world is it getting 360 from?"

    For excel 6:00 represents 6 hours (i.e. 6 AM) expressed in minutes as per the cell format 6 hours by the 60 minutes that every hour has, equals 360 minutes.

    for 6 minutes enter the value as 00:06:00

    for 6 seconds enter the value as 00:00:06

    The Cell Format ONLY changes the appearance of the value in the cell, not the value itself.

    You must enter the time values correctly in the following format "hh:mm:ss" regardless the visual output of the Custom Formatting.

    Image

    For the tracking exercise sheet,

    I would choose to rename the TIME column to TIME(in seconds) then enter the values as General numbers

    Per Example

    60 for 1 min

    100 for 1 min 40 sec

    360 for 6 min

    Another Option

    Create 2 columns TIME(min) and TIME(sec) the rest is obviously easy.

    I hope this helps you

    Regards

    Jeovany

    0 comments