Formula doesn't completely work

[phillipcook]

Hi Guys

I have the following formula:

=IF(G2="","",IF(NETWORKDAYS(B2,B2,V2:V22)>0,(H2-G2)-(MIN(18/24,H2)-MAX(6/24,G2)),0))

It seems to work 100% correct if the time consists of a proportion of it between 6am and 6pm, however what I am finding is if the start and finish times are both before 6am of if they are both after 6pm than the formula doesn't work.

for example I have a start time of 22:45 and a finish time of 23:45 which should equal 1 hour, however for some reason it is returning a total hours of 5:45???

Is there a way to fix this? my other thought was to calculate it out in 3 different cells. the first cell to work out the time between the start time and 6am (as long as it is Monday-Friday and not a public holiday (V2:V22)), the next cell time between 6pm and the finish time (as long as it is Monday-Friday and not a public holiday (V2:V22)) and lastly the 3rd cell which is simply a calculation of the two cells.

Let me know what you guys think?

Thanks in advance.

 

[jorismoerings]

Re: Formula doesn't completely work - Please help

So basically, the normal base rate of pay is between 6am and 6pm Monday till Friday and excluding public holidays. What this formula is meant to do is calculate the total hours worked that are within the 6am to 6pm (which it does), however if both of the star and finish times are pre 6am or if they are both post 6pm that’s when the formula seems to be playing up. If the day is not between Monday to Friday, is a public holiday or if the hours worked between 6am and 6pm equal 0 then the formula should show a blank.

I think when both start and finish times are both before or both after then I think it’s seeing a negative time.

Did you look at my latest example?

 

[phillipcook]

I did, however I kept getting a message when I hit enter that says there is a problem with the formula.

 

[jorismoerings]

can you share the error you've getting combined with your expected result.

 

[phillipcook]

There is a problem with your formula.

Not trying to write a formula?
When the first character is an equal (=) or a minus (-) sign, Excel thinks its a formula:
You type: =1+1, cell shows: 2
To get around this type an apostrophe (') first
You type: '=1+1, cell shows: =1+1

This is the formula I have written in (its in row 302)

=if(or(G302="",H302=""),"",(H302-G302)-((NETWORKDAYS(((B302+G302),(B302+H302),$AA$2:$AA$22)-1)*("18:00:00","06:00:00")+if(NETWORKDAYS((B302+H302),(B302+H302),$AA$2:$AA$22),MEDIAN(MOD((B302+H302),1),"18:00:00","06:00:00"),"18:00:00")-MEDIAN(NETWORKDAYS((B302+G302),(B302+G302),$AA$2:$AA$22)*mod((B302+G302),1),"18:00:00","06:00:00")))

Sorry I couldn't work out how to do a screen shot and post it on here