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

Hi,

This is difficult to answer because you're not telling what your aim is.
It looks like your aim is to calculate elapsed time within - and outside - shift hours.
Is that what your looking for and what should be the result of the calculation; do you actually need the distinction between the time elapsed within and outside of the shift hours?

 

[phillipcook]

Re: Formula doesn't completely work - Please help

Hi mate.

so this calculation is for Monday - Friday overtime. Our normal hours are 6am to 6pm. What this calculation is meant to do is check to see if the G2 has a time value. If it doesn’t then the formula doesn’t calculate and the cell remains blank. . If it does then it looks at the date in B2 to work out if it is a weekday (Monday to Friday) and also checks that it is not a public holiday (listed from W2:W22). If it is a weekday and not a public holiday it is then meant to calculate total hours worked from 00:00-06:00 and 18:00-24:00.
So for example if the start time was 04:00 and the finish time was 19:00, the calculation should figure out that a total of 3 hours were worked. Or if the start time was 19:00 and the finish time was 23:00 it should calculate the hours worked to be 4.0 hours.

Hope that make sense

 

[jorismoerings]

Re: Formula doesn't completely work - Please help

Hi,

something like this:

Book1
	B	G	H	I
1	Date	Start	End	OT
2	15-5-2018	04:00	19:00	03:00
3	16-5-2018	19:00	23:00	04:00
Sheet1

Cell Formulas
Range	Formula
I2	=IF(G2="","",(H2-G2)-((NETWORKDAYS((B2+G2),(B2+H2),W2:W22)-1)*("18:00:00"-"06:00:00")+IF(NETWORKDAYS((B2+H2),(B2+H2),W2:W22),MEDIAN(MOD((B2+H2),1),"18:00:00","06:00:00"),"18:00:00")-MEDIAN(NETWORKDAYS((B2+G2),(B2+G2),W2:W22)*MOD((B2+G2),1),"18:00:00","06:00:00")))

 

[phillipcook]

Re: Formula doesn't completely work - Please help

Hi Mate

I tried the code,but whats happening now is if the formula should equal 0 (so no OT on the day), it is coming up with a heap of ##### instead?

 

[phillipcook]

Re: Formula doesn't completely work - Please help

would the best way around this be 3 separate formulas? so 1 cell/ formula would:
Calculate hours between start time and 6am if the date is Monday-Friday and not a public holiday and if the date is not within Monday to Friday or the start time is blank than the calculation remains blank. if the total hours is a less than or equal to 00:00 then the cell is blank.
The next cell would:
Calculate the hours from 6pm to the finish time if the date is Monday-Friday and not a public holiday and if the date is not within Monday to Friday or the start time is blank than the calculation remains blank. if the total hours is a less than or equal to 00:00 then the cell is blank.
The last cell would simply be a calculation between the two other cells.

Would this be easier?

 

[jorismoerings]

Re: Formula doesn't completely work - Please help

Hi Mate

I tried the code,but whats happening now is if the formula should equal 0 (so no OT on the day), it is coming up with a heap of ##### instead?

Hi,

Could be caused by the cell format. Formula assumes a date/time formatted cell;
Did you format the cell as Time-related format or as a Number?

If the cell needs to be a number, try this: =(IF(G2="","",(H2-G2)-((NETWORKDAYS((B2+G2),(B2+H2),W2:W22)-1)*("18:00:00"-"06:00:00")+IF(NETWORKDAYS((B2+H2),(B2+H2),W2:W22),MEDIAN(MOD((B2+H2),1),"18:00:00","06:00:00"),"18:00:00")-MEDIAN(NETWORKDAYS((B2+G2),(B2+G2),W2:W22)*MOD((B2+G2),1),"18:00:00","06:00:00"))))*24

 

[phillipcook]

Re: Formula doesn't completely work - Please help

The cell is formatted as [HH]:mm

the cell definitely needs to be a time format.

The issue seems to be caused if both the start and finish times are both before 6am or both after 18:00. So for example 03:00 till 05:30 should display this formula as a blank cell because the time between 6am and 6pm is 0, however the cell is showing ######### because I think it’s swing a negative time. If for example the start time was 03:00 with a finish time of 10:00, the formula is working and returning a result of 04:00.

If we can figure out a way to make the formula show “” if the result is a negative number then the formula should work perfectly.

 

[jorismoerings]

Re: Formula doesn't completely work - Please help

Hi,

I've lost you. The formula was aimed at

If it is a weekday and not a public holiday it is then meant to calculate total hours worked from 00:00-06:00 and 18:00-24:00

Below are the results of my formula with most options, so start and end times as Before/Before, Before/Within, Before/After, Within/Within, Within/After, After/After and i can't reproduce the ##### as a result beside if there's no end time filled in and i corrected that in the formula.

So from my perspective this should do the trick.
Can you elaborate more explicitly what's not working?

Book1
	B	G	H	I	J	K
1	Date	Start	End	OT	Start	End
2	15-5-2018	03:00	05:30	02:30	Before	Before
3	15-5-2018	03:00	17:30	03:00	Before	Within
4	15-5-2018	05:30	17:30	00:30	Before	After
5	15-5-2018	07:30	17:30	00:00	Within	Within
6	15-5-2018	16:30	22:30	04:30	Within	After
7	15-5-2018	18:15	22:30	04:15	After	After
8	15-5-2018	18:15			After	No Endtime
Sheet1

Cell Formulas
Range	Formula
I2	=IF(OR(G2="",H2=""),"",(H2-G2)-((NETWORKDAYS((B2+G2),(B2+H2),W2:W22)-1)*("18:00:00"-"06:00:00")+IF(NETWORKDAYS((B2+H2),(B2+H2),W2:W22),MEDIAN(MOD((B2+H2),1),"18:00:00","06:00:00"),"18:00:00")-MEDIAN(NETWORKDAYS((B2+G2),(B2+G2),W2:W22)*MOD((B2+G2),1),"18:00:00","06:00:00")))

 

[phillipcook]

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.