+ Reply to Thread
Results 1 to 18 of 18

writing numbers as simple hh:mm but without AM/PM

  1. #1
    Registered User
    Join Date
    05-14-2016
    Location
    New Delhi, India
    MS-Off Ver
    Office Professional 2013
    Posts
    7

    writing numbers as simple hh:mm but without AM/PM

    I'm creating a simple timesheet, where my team can enter simple values of time as Hours and minutes and no AM/PM involved.

    So here is my problem, when i'm formatting any cell into time it's converting the value either to hh:mm am/pm or 1200 hrs format. But i don't want that value to be converted to any particular time of a day.

    I want is simple hours:minutes format, where values can be greater than 24 hrs.

    Any help would be highly appreciated .

    Thanks in Advance!

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: writing numbers as simple hh:mm but without AM/PM

    Maybe custom format of

    [h]:mm

  3. #3
    Registered User
    Join Date
    05-14-2016
    Location
    New Delhi, India
    MS-Off Ver
    Office Professional 2013
    Posts
    7

    Re: writing numbers as simple hh:mm but without AM/PM

    i tried but did not work for me.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: writing numbers as simple hh:mm but without AM/PM

    How are you entering the times?

    Type some times into the forum, exactly as you want to type them into excel (Not how you want them formatted).

  5. #5
    Registered User
    Join Date
    05-14-2016
    Location
    New Delhi, India
    MS-Off Ver
    Office Professional 2013
    Posts
    7

    Re: writing numbers as simple hh:mm but without AM/PM

    4:30 is 4 hours and 30 minutes not 4:30 AM.

    or

    40:20 should mean 40 hours 20 minutes.

    Also, the calculation i want is
    A1 = 20:50 (20 hours 50 minutes), B1 = 9:10 (9 hours 10 minutes), C1 (A1-B1) and should give the result as 11:40 (11 hours 40 minutes)

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: writing numbers as simple hh:mm but without AM/PM

    The format that I suggested is correct for the scenario that you describe.

    Are you entering the times into excel in time format, or in numeric format?

  7. #7
    Registered User
    Join Date
    05-14-2016
    Location
    New Delhi, India
    MS-Off Ver
    Office Professional 2013
    Posts
    7

    Re: writing numbers as simple hh:mm but without AM/PM

    i'm entering values as 30:20.

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: writing numbers as simple hh:mm but without AM/PM

    Then it should work.

    How does the custom format that I suggested differ from what is expected?

  9. #9
    Registered User
    Join Date
    05-14-2016
    Location
    New Delhi, India
    MS-Off Ver
    Office Professional 2013
    Posts
    7

    Re: writing numbers as simple hh:mm but without AM/PM

    okay, the moment A1 > 24 problem starts

    A1 = 50:50 (50 hours 50 minutes), B1 = 10:40 (10 hours 40 minutes), C1 (A1-B1) and should give the result as 40:10 (40 hours 10 minutes)

    but it's showing 16:10

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: writing numbers as simple hh:mm but without AM/PM

    Then you haven't applied the custom format to C1.

  11. #11
    Registered User
    Join Date
    05-14-2016
    Location
    New Delhi, India
    MS-Off Ver
    Office Professional 2013
    Posts
    7

    Re: writing numbers as simple hh:mm but without AM/PM

    Worked ! THANKS. One last thing, what if A1<B1 ? its showing #########

  12. #12
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: writing numbers as simple hh:mm but without AM/PM

    Excel cannot display negative times , that is why you see ######

    If you need to display times as negative then there is a workaround. Do you know how to apply conditional formatting?

  13. #13
    Registered User
    Join Date
    05-14-2016
    Location
    New Delhi, India
    MS-Off Ver
    Office Professional 2013
    Posts
    7

    Re: writing numbers as simple hh:mm but without AM/PM

    No. I'm new to this. Can you share the steps how to do that?

  14. #14
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: writing numbers as simple hh:mm but without AM/PM

    Ok, First of all, you need to change the formula in C1 to

    =ABS(A1-B1)

    This turns the negative value into a positive value so that it displays correctly.

    Next select column C (the entire column, by clicking the column letter at the top).

    Go to the 'Home' tab on the ribbon, then click 'Conditional Formatting' followed by 'New Rule'

    In the top part of the pop-up box, click 'Use a formula to determine which cells to format'

    You should then see another box labelled 'Format values where this formula is true:' in this box, enter the formula =$A1<$B1 (don't press enter).

    Click the 'format' button, then click the 'Number' tab on the new pop-up window.

    Click 'Custom'

    In the box under 'Type' delete the existing content, then enter the custom format "-"[h]:mm

    Click the 'OK' buttons until you get back to excel.

    Hopefully I haven't missed a step anywhere.

  15. #15
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: writing numbers as simple hh:mm but without AM/PM

    Quote Originally Posted by jason.b75 View Post
    Excel cannot display negative times
    It can if you use the 1904 date system.

    However, if you don't know what you're doing I would avoid this!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  16. #16
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: writing numbers as simple hh:mm but without AM/PM

    Useful to know, Tony.

  17. #17
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: writing numbers as simple hh:mm but without AM/PM


  18. #18
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: writing numbers as simple hh:mm but without AM/PM

    Personally, I've not had the need to consider the use of the 1904 system, but I have encountered the problems it can cause in several past threads.

    Think I will continue to avoid it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [Help Requested] Writing Simple Macro for 9 Week Due Date
    By RSExcel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-25-2016, 12:42 PM
  2. Paid Help Wanted - Writing Simple VBA Game companion
    By NewYears1978 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-18-2015, 03:11 AM
  3. Help with writing a simple range programme in VBA
    By jembop in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-06-2014, 06:15 AM
  4. Replies: 2
    Last Post: 05-17-2013, 03:00 AM
  5. Writing a simple fill in program
    By jackleman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2007, 08:01 PM
  6. [SOLVED] Writing a simple macro or formula
    By Robin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-28-2006, 07:15 PM
  7. need help in writing simple macro
    By rkg in forum Excel General
    Replies: 4
    Last Post: 05-04-2005, 07:03 PM

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