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

1. ## 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.  Register To Reply

2. ## 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?  Register To Reply

3. ## 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.  Register To Reply

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

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

where A2 is the starting and B2 the finishing.  Register To Reply

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?  Register To Reply

6. ## 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).
Regards  Register To Reply