+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 22 of 22

Display times in a 12-hour format without the AM or PM displayed in the cell

  1. #16
    Registered User
    Join Date
    04-23-2019
    Location
    U.S.
    MS-Off Ver
    Excel 2016
    Posts
    70

    Re: Display times in a 12-hour format without the AM or PM displayed in the cell

    Hi Aardigspook,
    While working on the calendar form you helped me with I noticed something odd that I thought you might be interested in seeing. I'm sending back the file you sent me, wherein I thought the 1:00/13:00 display had been solved, and for the most part it has. But...when copying the formula over to a new spreadsheet calendar format, I noticed the 13:00 popping up. I thought I was doing something wrong, until I noticed that all start times didn't respond the same re the 1:00/13:00. So I looked more closely at the file you sent and I noticed the same thing, which you will see as well. I've highlighted where the 13:00 is popping up in some of the columns, and the strange thing is, even in the same column, 1:00 appears in the next 12 hour cycle after a 13:00. You will also notice I highlighted the 1:00 times on the 3rd day, where there is no problem, 1:00 appears in both 12 hour cycles. Basically, you will see that the formula isn't consistent, sometimes it display 1:00 sometimes 13:00, and as mentioned, this can happen in the same column. Any ideas on a fix?

  2. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    36,042

    Re: Display times in a 12-hour format without the AM or PM displayed in the cell

    You might wish to remove the SOLVED tag from the thread ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #18
    Registered User
    Join Date
    04-23-2019
    Location
    U.S.
    MS-Off Ver
    Excel 2016
    Posts
    70

    Re: Display times in a 12-hour format without the AM or PM displayed in the cell

    Good point. Thanks. The SOLVED tag has been removed.

  4. #19
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    2,267

    Re: Display times in a 12-hour format without the AM or PM displayed in the cell

    Sorry, my fault . There was nothing wrong with Rick's basic formula, just with how I converted it to refer to a start time above instead of to a column to the left. I put the '-1/24' in the wrong place.

    Put this in C4 instead and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    As before, the file with it working is attached.
    My apologies again for the hassle.
    Regards,
    Aardigspook

    My internet connection can be intermittent, so sometimes I may not reply quickly to questions. Sorry - it's not personal and I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  5. #20
    Registered User
    Join Date
    04-23-2019
    Location
    U.S.
    MS-Off Ver
    Excel 2016
    Posts
    70

    Re: Display times in a 12-hour format without the AM or PM displayed in the cell

    Thanks Aardigspook for providing the remedy so promptly! It was also thoughtful of you to highlight all the 1:00s.
    I hope you have good day.
    Bryan

  6. #21
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    2,267

    Re: Display times in a 12-hour format without the AM or PM displayed in the cell

    You're welcome, sorry that I didn't give you the correct version the first time!
    All the best,
    AS

  7. #22
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,862

    Re: Display times in a 12-hour format without the AM or PM displayed in the cell

    In C4 then copy down

    =MAX(1/24,MOD(C3+(1/48),12.9/24))

    Apply for other ranges
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1