Share via

Defaulting to PM isntead of AM in Excel using the time format

Anonymous
2018-06-06T22:56:07+00:00

Hello! I'm making a time sheet in excel to track my work hours and I always go in in the AM and leave in the PM. I would like to be able to make it default to PM when I put in my "time out" time. For example, say I work from 9:00AM to 5:00PM if I put that into the timesheet and use enter to autofill the time it always defaults to a AM and I have to manually change it to PM. It's really stupid but it's just a quality of life improvement that I was wondering if it was possible. Any help is much appreciated!

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

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lz365 38,201 Reputation points Volunteer Moderator
    2018-06-07T10:58:18+00:00

    @ExcallyPurr

    IF problem understood. Data stored in a Table

    Start time format: hh:mm AM/PM

    End time format: hh:mm "PM"

    Worked hrs formula: =(([End time]+"12:00:00")-[Start time])*24

    Worked hrs: 0**,**00" Hrs"              (change the comma for a dot - I use a different separator)

    With this, if I input ie. 06:30 in the End time column it displays 06:30 PM (althought the actual value is 06:30 AM)

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2018-06-07T10:39:12+00:00

    For 5PM are you trying to enter as 5:00 PM    or as  17:00 ?

    I believe if the cell is formatted with am/pm and if the user enters the data as 17:00 then Excel will automatically display it as 5:00PM

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments