+ Reply to Thread
Results 1 to 6 of 6

Trying to create a formula to count days based on 8 hour days

  1. #1
    Registered User
    Join Date
    10-28-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    5

    Trying to create a formula to count days based on 8 hour days

    I am creating a spreadsheet that counts the days since a specific request was created. I have been tasked with including the time that it was started and completed. My problem is that I need this to be based on an 8 hr day instead of a 24 hr day, so that if a request only last 4 hours then it comes up as .5 of a day. I have been trying to research what I am looking at doing and have not come across anything that helps.

    Any help is greatly appreciated.

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Trying to create a formula to count days based on 8 hour days

    Hi
    Clarify me. If any work is started on 01/22/2016 15:00 and has finished on 01/23/2016 15:00 then the number of hours is 3*24=72 hours?

  3. #3
    Registered User
    Join Date
    10-28-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    5

    Re: Trying to create a formula to count days based on 8 hour days

    I want it to be formatted with total days as a whole number with the remaining hours as decimals. So... starting on 01/1/16 12:00 PM and finishing on 01/2/16 4:00 PM. I want it to result in 1.5 days. The 1 day for the Noon to Noon period but then I want it to count off an 8 hour day to result in the .5 for the additional 4 hours.

  4. #4
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Trying to create a formula to count days based on 8 hour days

    Try these
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where A2 is the starting and B2 the finishing.

  5. #5
    Registered User
    Join Date
    10-28-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    5

    Re: Trying to create a formula to count days based on 8 hour days

    That seems to be working! Would you mind explaining what makes this work for 8 hours?

  6. #6
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Trying to create a formula to count days based on 8 hour days

    Hi
    The decimal portion of the date represents a fraction of the day. When this part is multiplied by 24 represents the number of hours since midnight.
    Thus 1/24 = 0.0416666666666667 is 1 hour in a 24 hour day. One hour in an 8 hour day corresponds to 1/8 = 0.125 and (1/8) / (1/24) = 3 is the multiplier that transforms 8 in 24.
    So we just have to multiply the decimal part of the dates difference by 3 MOD(B2-A2,1)*3 and add the number of whole days that is the integer part INT(B2-A2).
    Sorry for my bad english.
    Regards

+ 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: 17
    Last Post: 08-03-2015, 06:15 AM
  2. Replies: 0
    Last Post: 06-11-2015, 03:12 AM
  3. [SOLVED] Count 6 working days (excluding Sunday, Holidays & half days)
    By eve_star1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-29-2015, 08:00 AM
  4. Count Days, and last transaction days
    By ssttuu in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-04-2013, 08:35 AM
  5. Count of days (ex: Monday plus 2 days = 1 for Mon, Tues, Wed)
    By melissadolan2004 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-25-2013, 10:07 PM
  6. How to count # of days in a month from a list of days
    By vinnygamz in forum Excel General
    Replies: 3
    Last Post: 07-20-2009, 05:02 PM
  7. Calculating time elapsed in days and count days within same range
    By michellem410 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-19-2008, 01:13 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