+ Reply to Thread
Results 1 to 7 of 7

Converting days 0:0 hrs:min to hours

  1. #1
    Registered User
    Join Date
    05-23-2014
    Location
    Fareham, England
    MS-Off Ver
    2010
    Posts
    10

    Converting days 0:0 hrs:min to hours

    Hello i am trying to calculate hours worked for a friend, i have used the formula =DATEDIF(B32,C33,"d")&" Days "&TEXT(C33-B32,"h:m")&" hrs:min" to convert the days/hours worked to the format "days 0:0 hrs:min" but i am unsure how to either modify the formula or convert the result to total hours worked?
    Can anybody help please?

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,341

    Re: Converting days 0:0 hrs:min to hours

    Try simply:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,341

    Re: Converting days 0:0 hrs:min to hours

    Or even:
    Try simply:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and format the cell with formula as "[h]:mm"

    other option:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and format this cell as General or as number with desired decimal places

  4. #4
    Registered User
    Join Date
    05-23-2014
    Location
    Fareham, England
    MS-Off Ver
    2010
    Posts
    10

    Re: Converting days 0:0 hrs:min to hours

    Hi sorry i should have added that the format of her start and stop times is in "dd/mm/yyyy 00:00" as she works three shifts which do run overnight to the next day. Teh formula i have used gives me days 0:0 hrs:min.

  5. #5
    Registered User
    Join Date
    01-30-2013
    Location
    Hobart, TAS
    MS-Off Ver
    Office 2003, XL2007
    Posts
    46

    Re: Converting days 0:0 hrs:min to hours

    A1 is start date & time
    B1 is finish date & time (format both cells as d/mm/yyyy hh:mm or use your local convention)
    C1 =B1-A1 (format as d "days" hh:mm)

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,341

    Re: Converting days 0:0 hrs:min to hours

    Have you really tried to use my suggestions? with square brackets? and with 24*(dayandtimedifference)?
    See attached.

    If by "total" you mean, you have a problem with summing it and showing total hours worked in shifts written in rows say 20:40 you can sum endpoins deduct sum of startpoints and express this in hours, so something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-23-2014
    Location
    Fareham, England
    MS-Off Ver
    2010
    Posts
    10

    Re: Converting days 0:0 hrs:min to hours

    YEs that works great Kaper, thank you very much

+ 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. Converting days hours minutes to hours
    By namip in forum Excel General
    Replies: 7
    Last Post: 07-10-2011, 02:00 PM
  2. Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..
    By krfarmer in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-25-2010, 05:21 AM
  3. Converting hours into days, hours & minutes
    By John754 in forum Excel General
    Replies: 3
    Last Post: 10-07-2009, 06:28 PM
  4. Problem converting Hours to Days, Hours, Minutes
    By Zyzzx in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-24-2005, 12:05 PM
  5. converting hours to days,hours,minutes
    By L_n_da in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-29-2005, 02:05 PM

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