Formula to deduct 30 minutes every 5 hours

Hi guys.....

I have this formula, that I copied from an excel template (One of the time sheets)
I kinda understand the formula, but I can't adapt it to fit my specific needs.
The formula is as follows:

=IF((OR(C6="",C5="")),0,IF((C6<C5),((C6-C5)*24)+24,(C6-C5)*24))

Where C5 = time Start
C6= time end

This formula works perfect, but I just need the following alteration:
for every 5 hour, the formula has to deduct 30 minutes from the result

So, if the difference between time start and end = 8.5 hours, it has to deduct 30 min, leaving 8 hours
If the difference is 12 hours, it has to deduct 1 hour, leaving 11 hours

I would also appreciate an explanation of the formula proposed.....because it took me like 5 hours to understand the above written formula.

Hope I can get some help

hi LionelSpratt, welcome to the forum. please change your title to something more descriptive of the problem. for eg. Formula to deduct 30 minutes every 5 hours.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

without confusing you further, let's just use your existing formula & make some adjustments. let's get rid of the unnecessary brackets first:
=IF(OR(C6="",C5=""),0,IF(C6<C5,(C6-C5)*24+24,(C6-C5)*24))

so if C6 or C5 is blank, show as 0. if C6 lesser than C5, then (C6-C5)*24+24. let's put in this exact formula to calculate the 30 minutes every 5 hours using INT. INT will only take in the integer of the results. if time is 11:30 pm to 8 am, then it's 8.5 hours. that's what (C6-C5)*24+24 will give us. cover it with brackets & divide it by 5 hours.
=((C6-C5)*24+24)/5
you would get 1.5. so we only have a single 5 hour to deduct. so by putting the INT formula, we can get 1, ignoring the decimal
=INT(((C6-C5)*24+24)/5)
what we need now is to get 0.5 hours. so simply multiply by 0.5
=INT(((C6-C5)*24+24)/5)*0.5

join this up with your existing formula & you would have:
=IF(OR(C6="",C5=""),0,IF(C6<C5,(C6-C5)*24+24-INT(((C6-C5)*24+24)/5)*0.5,(C6-C5)*24))

do the same for the last part of the formula. (C6-C5)*24:
=INT(((D6-D5)*24)/5)*0.5

so the final formula would be:
=IF(OR(C6="",C5=""),0,IF(C6<C5,(C6-C5)*24+24-INT(((C6-C5)*24+24)/5)*0.5,(C6-C5)*24-INT(((C6-C5)*24)/5)*0.5))

Welcome to the forum Lionel, thank you for joining the community.
Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.