+ Reply to Thread
Results 1 to 9 of 9

time formats and time duration formula

  1. #1
    Registered User
    Join Date
    01-27-2018
    Location
    Fort Lauderdale, FL
    MS-Off Ver
    Microsoft for MAC 16.9
    Posts
    2

    time formats and time duration formula

    I am creating a spreadsheet to track my time and hours worked. I have my Start Time and my End Time in h:mm AM/PM format, I then have a column for total hours worked in hh:mm format. Something like this: Column A 9:00 AM, column B 4:30 PM, and my total hours worked column = 7:30 (meaning 7.5 hours worked) my formula there is =B1-A1. I am getting the duration of time lapsed between 9am and 4:30pm. That's fine.
    My question is, I want my column B to default to PM so I don't have to type it. When I change the format to show PM, it puts it in military time. I want standard time. So, I did a custom format of h:mm "PM". That didn't work. It turned my "total hours worked" column into this ############### value. Can I have both, standard time and my duration of time formula?

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: time formats and time duration formula

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Forum Contributor
    Join Date
    03-30-2016
    Location
    Gillette, WY
    MS-Off Ver
    2016
    Posts
    220

    Re: time formats and time duration formula

    Time formats are notoriously difficult, at least for me, to deal with. You may have to deal with typing in AM/PM if you don't want to deal with a 24-hour time format. I don't know if there is a way for excel to automatically distinguish between 9:00AM and 9:00PM without the AM/PM, at least not without vba. I'm playing with it, will see what I can come up with.

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    15,848

    Re: time formats and time duration formula

    If you type in a time, Excel has to know what time it is. It does not have a built-in way for you to type 4:30 and for it to read your mind that you mean 4:30 PM. However, what you want can be done with a macro. The macro below looks at the time entered in column B, and if it is an AM time it converts it to PM.

    Please Login or Register  to view this content.
    Example attached.
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: time formats and time duration formula

    you can try this for duration: =IFERROR(IF((B2-A2)<0,"",B2-A2),"") or =IFERROR(IF(OR(ISBLANK(A2),ISBLANK(B2),(B2-A2)<0),"",B2-A2),"")
    the rest is a custom format like: hh:mm AM/PM
    Last edited by sandy666; 01-27-2018 at 10:43 PM.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,763

    Re: time formats and time duration formula

    Do not type in your start & end times. Use Ctrl+shift+: in the appropiate cell.
    Format columns A & B using standard Excel Time Format from the number format menu: Select 1:30 PM from the list.
    Format the duration column as [h]:mm if using =B1-A1.

    If you want decimal hours, use:=24*(B1-A1) and format as number with 2 decimals.
    Ben Van Johnson

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    15,848

    Re: time formats and time duration formula

    Quote Originally Posted by protonLeah View Post
    Do not type in your start & end times. Use Ctrl+shift+: in the appropiate cell.
    This is a very good point. CTRL+: enters the current time. If you use this then you don't have to type in a time at all, and you don't need my code.

  8. #8
    Registered User
    Join Date
    01-27-2018
    Location
    Fort Lauderdale, FL
    MS-Off Ver
    Microsoft for MAC 16.9
    Posts
    2

    Thumbs up Re: time formats and time duration formula

    I tried your formula and it works great 6StringJazzer. But what is the actual code you have in the End Time cell?

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: time formats and time duration formula

    If you're wanting to just always assume B is PM
    Go ahead and just type it as an AM time (same as A), then use this formula to calculate the duration
    =(B1+0.5)-A1

+ Reply to Thread

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