+ Reply to Thread
Results 1 to 2 of 2

Breakdown total shift hours into days/hours/minutes

  1. #1
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Breakdown total shift hours into days/hours/minutes

    Hi,

    I'm looking for a formula that will convert the number of hours into a breakdown of total days/hours/minutes in the table below. A typical shift is 7.5 hours (minus a 30 minute lunch) which is the equivalent of 1 day.

    Cell A2 is where you key in your value.
    Cells B2 to D2 display the results and cell E2 displays the message.

    https://docs.google.com/spreadsheets...e7k/edit#gid=0

    If you key in a value of 7.5, the respective cells should tell you it is 1 day (and 0 hours/minutes)

    And if you key in 9 (someone worked an extra hour and half), the formula should display 1 day / 1 hour / 30 minutes.

    Is this possible?
    Last edited by exceleratevba; 12-05-2020 at 09:34 PM.

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

    Re: Breakdown total shift hours into days/hours/minutes

    In B2
    =INT(A2/7.5)
    In C2
    =INT(MOD(A2/7.5,1)*7.5)
    In D2
    =ROUND((MOD(A2/7.5,1)*7.5-C2)*60,0)
    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

+ 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. Replies: 15
    Last Post: 09-16-2020, 09:39 PM
  2. Replies: 8
    Last Post: 05-12-2017, 11:04 AM
  3. Replies: 3
    Last Post: 07-21-2013, 05:45 PM
  4. [SOLVED] Function or macro to convert string with weeks, days, hours, minutes to Hours
    By kknb0800 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2012, 11:35 AM
  5. [SOLVED] Decimal Time to Hours and Minutes over 24 hours in total
    By FlyingTiger in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-17-2012, 06:18 AM
  6. Breakdown total minutes into hours & mins
    By arlu1201 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2011, 08:03 AM
  7. [SOLVED] Problem converting Hours to Days, Hours, Minutes
    By Zyzzx in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-24-2005, 12: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