Assistance with a formula to return values based on time

[spittingfire]

Hi All,

I have the following issue with a formula in the attached workbook. The formula is located in columns "N" & "P".

The issue is if I have a split shift as illustrated in the highlighted rows and the person only wants a portion of one of the splits off. The formula should be able to differentiate between the start times (Columns D or E) and only put the appropriate time in columns N or P while leaving the other split shift blank.

The rows highlighted in "Yellow" is the issue that I am facing and the bottom 4 rows in "Green" is the results I am looking to get.

Thanks in advance for you assistance.

[Bob Phillips]

Can you explain each of those rows as to why they should be the results in green, I cannot see the logic.

[spittingfire]

Thanks for your reply xld,

The sheet is an attendance sheet and when I did this it served it's purpose well until they introduced split shifts.

The original formula for Column N is:
=IF(AC2=AD2,"",IF(A2="","",IF(OR($K2="0:00",$J2="PAY-"),"",IF($H2="",$D2,IF(H2<D2,"",IF($H2>$D2,$H2,$D2))))))

and for column P:
=IF(AC2=AD2,"",IF(A2="","",IF(OR($K2="0:00",$J2="PAY-",IFERROR(INDEX($AF$1:$AF$20,MATCH(K2,$AE$1:$AE$20,0)),K2)<IF(I2="",F2-D2,I2)),IF($H2="",$D2,IF($H2>$D2,$H2,$D2))+IF(N2="",0,K2),"")))

In an effort to try to deal with the split shift situations I tried to do different things to return a "blank" where the portion of the split is not impacted but to no avail hence why I turned to the forum for help.

Now to try to explain what is going on - the highlighted rows is a sample of a split shift. The first portion of the shift is as listed there is 9 am - 1 PM and the second part of the shift is 6:30 PM to 10 PM.

The issue is the person called in and requested to be coded off for 3.5 hrs but starting at 6:30 PM (as in the first two highlighted rows). Since the shift time that the person wants coded off is from 6:30 PM - 10:00 PM just that shift should be touched while the other shift of 9 am to 1 PM should not be touched.

The same goes for the second example as well.

The end result is that I need to (if possible) have something written where those split shifts are taken into account.

One thing I did notice and thought I could use is the shift duration in column AD. I have updated the sheet with the correct information from that column. Those times are fixed times for the split with the first portion being 240 minutes and the second portion being 210 minutes.

So the bottom 4 rows is the result I would like to see if possible. Meaning that whatever formula I can get in Columns N & P will also be able to maybe also look at the start time of the split and maybe cross reference against the duration of column AD to decide which portion of the split to use.

One note the reason why I can not use column H in this case is because they can ask for any time during the shift. I think since the duration of the splits are fixed I think that may be the best bet.

I tried to put this into the original formula (IF(AND(H2<>"",AD2=210,),"",IF(AND(H2<>"",AD2=240),"",) but I can't get it to work because I think I need to re-write the formula but I am getting stuck.

[spittingfire]

After taking another look I think I may have been using the wrong columns. I re-wrote the formula to =IF(AC2=AD2,"",IF(A2="","",IF(AND(H2>F2,AD2=210,),"",IF(AND(H2>F2,AD2=240),"",IF(OR($K2="0:00",$J2="PAY-",IFERROR(INDEX($AF$1:$AF$20,MATCH(K2,$AE$1:$AE$20,0)),K2)<IF(I2="",F2-D2,I2)),IF($H2="",$D2,IF($H2>$D2,$H2,$D2))+IF(N2="",0,K2),""))))) in Column P and N to
=IF(AC2=AD2,"",IF(A2="","",IF(OR($K2="0:00",$J2="PAY-"),"",IF($H2="",$D2,IF(AND(H2>F2,AD2=210,),"",IF(AND(H2>F2,AD2=240),"",IF($H2>$D2,$H2,$D2))))))) and it seems to be working but will test for a couple days and will respond back.