Error with if formulas to split shifts based on time

[mshah123]

Hello,

I am working on a project where I need to split hours between departments. So for example if a shift was done from 7am to 7pm it would be coded to the department in Department 1 (column A). If it was worked from 7pm to 7am it would be coded to the department in Department 2 (column B). If there was an overlap then I want it to say "Split".

Normally I am quite good with If statements but I can't get this one to work. I'm not able to attach a sample of my document but I can describe the layout:

Shift Day</SPAN>	Day Start</SPAN>	Day Finish</SPAN>	Department
0700-1900</SPAN>	0700</SPAN>	1900</SPAN>	A</SPAN>
1000-2200</SPAN>	1000</SPAN>	2200</SPAN>	Split</SPAN>
0800-2000</SPAN>	0800</SPAN>	2000</SPAN>	Split</SPAN>
0700-1900</SPAN>	0700</SPAN>	1900</SPAN>	A</SPAN>
1500-0300</SPAN>	1500</SPAN>	0300</SPAN>	B</SPAN>

<TBODY>
</TBODY>

As you can see the first four rows of data are correct. But I keep getting errors with ones similar to the last one, which should show as "Split".


My formula at the moment is:
=IF(AND(K2>="0700",L2<="1900",K2<"1900",L2>"0700",L2<>"0000"),H2,IF(OR(AND(K2>="1900",L2<="0700"),AND(K2>="1900",L2<="2400"),AND(K2>="1900",L2="0000"),AND(K2>"0000",L2<"0700")),I2,IF(OR(AND(K2<"0700",L2>"0700"),AND(L2>"1900",K2>"0700"),AND(K2<"1900",L2>="0000",L2<"0700")),"Split","")))

Where K is the "Day Start" column, L is the "Day Finish" column", column H is the department for the early shift and column I is the department for the night shift.

if anyone could help with this or tell me where I am going wrong then I would be really grateful. Also, I realise the formula is quite complex so if there's a simpler method of doing it that would be great.

Thanks,

Miran

</SPAN>

</SPAN>

</SPAN>

</SPAN>

<TBODY>
</TBODY>

 

[Ben M]

Hi Miran,

I've had a try at getting the outcome you are wanting.

Please have a look at the below and let me know if it's any use:

Free File Hosting, Free Video Hosting, Online File Hosting. Large Files and Images up to 5GB - YourFileLink.com

I think the number format plays a part in this. I have used a custom time format (hh:mm) which seems to help.

Best wishes,

Ben

 

[mshah123]

Hi Ben,

Thanks a lot for your help. It's definitely a simpler formula and it works on most of them, but there is just one issue...

If for example a shift started at 7am and finished at 9am that should still be "Department 1" as it falls between 7am and 7pm. Similarly, if a shift started at 9pm and finished at 4am that should still be "Department 2" as it falls between 7pm and 7am. But at the moment these are showing as "Split". Do you know if there is any way around this?

Thanks again,

Miran

 

[Ben M]

Hi Miran,

Sorry I've only just got the chance to have another look at this.

I've though of another solution although it's not as elegant as using an IF formula. I though that because there are so many variables I'd try a different approach using a lookup function.

I am assuming that shifts start on the hour or get rounded to the hour for accounting purposes, otherwise the lookup data would need to be reworked - a long process but not too difficult.

Let me know what you think

Free File Hosting, Free Video Hosting, Online File Hosting. Large Files and Images up to 5GB - YourFileLink.com

Best wishes,

Ben