+ Reply to Thread
Results 1 to 6 of 6

Time Format

  1. #1
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    78

    Time Format

    Hi there,

    I have a report from Salesforce which returns time to complete a task as say "5 days 18 hours 19 minutes" or "0 days 1 hours 23 minutes". Is there a way I can have this show in a way Excel can understand i.e. hh:mm or dd:hh:mm? I am using a pivot table to pull the data together so may need to reference the pivot table info to a new tab for the format to appear correctly.

    Any help would be appreciated.

    Thanks

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Time Format

    hi
    upload a sample sheet with example showing your result manually so we can help you

  3. #3
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    78

    Re: Time Format

    FYI attached sample report

  4. #4
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Time Format

    is this what you are trying
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Time Format

    In your report data, col L, I created a new formula for Time2

    =LEFT(A2,2)+MID(A2,FIND("hours", A2)-3,3)/24 + MID(A2,FIND("minutes",A2)-3,3)/(24*60)

    Then increased your pivot table to include that column and used the new column to sum results instead of column A.
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Registered User
    Join Date
    01-24-2017
    Location
    Glasgow
    MS-Off Ver
    365
    Posts
    78

    Re: Time Format

    ChemistB you have come up with a working solution I would never have thought of.

    Thank you for taking the time to look over this and to reply.

+ 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. [SOLVED] time sheet help, format TIME and formula based on the actual time entered.
    By bh mng in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-06-2016, 01:10 PM
  2. Format time from military time to standard time
    By Valencia0307 in forum Excel General
    Replies: 7
    Last Post: 06-01-2014, 11:15 AM
  3. [SOLVED] I want telephone numbers and time of any format to specific format
    By Latha Mani in forum Excel General
    Replies: 16
    Last Post: 01-11-2014, 09:58 AM
  4. Change format of lots of data cell to a time format
    By dazza67 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-24-2013, 03:24 PM
  5. Convert date and time in serial number format to text format
    By nda13112 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-11-2013, 02:45 PM
  6. Excel: How to convert time format into number format that can be added?
    By Frances Jones in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-17-2012, 04:10 PM
  7. Display time in time format instead of decimal format
    By CasualVisitor in forum Excel General
    Replies: 5
    Last Post: 07-03-2009, 06:24 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