How to consolidate, or compress this equation.

**GTFORCEMTB** · 06-11-2012, 06:45 PM

Hi forum,
I have been working on this time schedule spreadsheet and formula for my current work place. It works but it is very very long, can any one help me to shorten the (=IF) formula, thank you!

*edit I have added the workbook to the thread.

=IF(C3="9 to 7",10,IF(C3="9 to 5",8,IF(C3="9 to 6",9,IF(C3="9 to 1",4,IF(C3="11 to 7",8,IF(C3="Off",0,IF(C3="12 to 5",5,)))))))+IF(D3="9 to 7",10,IF(D3="9 to 5",8,IF(D3="9 to 6",9,IF(D3="9 to 1",4,IF(D3="11 to 7",8,IF(D3="Off",0,IF(D3="12-5",5,)))))))+IF(E3="9 to 7",10,IF(E3="9 to 5",8,IF(E3="9 to 6",9,IF(E3="9 to 1",4,IF(E3="11 to 7",8,IF(E3="Off",0,IF(E3="12 to 5",5,)))))))+IF(F3="9 to 7",10,IF(F3="9 to 5",8,IF(F3="9 to 6",9,IF(F3="9 to 1",4,IF(F3="11 to 7",8,IF(F3="Off",0,IF(F3="12 to 5",5,)))))))+IF(G3="9 to 7",10,IF(G3="9 to 5",8,IF(G3="9 to 6",9,IF(G3="9 to 1",4,IF(G3="11 to 7",8,IF(G3="Off",0,IF(G3="12 to 5",5,)))))))+IF(H3="9 to 7",10,IF(H3="9 to 5",8,IF(H3="9 to 6",9,IF(H3="9 to 1",4,IF(H3="11 to 7",8,IF(H3="Off",0,IF(H3="12 to 5",5,)))))))

**tigeravatar** · 06-11-2012, 07:01 PM

FTGORCEMTB,

Using your posted formula, this should work:

Please Login or Register  to view this content.

Note that if you have a start and end time in the same half of the day (as in, if both were AM or if both were PM, like "3 to 11" or "1 to 9") this formula wouldn't return a correct result.

**Richard Buttrey** · 06-11-2012, 07:06 PM

Hi,

One way would be to create a table of

Please Login or Register  to view this content.

and then use =VLOOKUP(C3,tableabove,2,False)+VLOOKUP(D3,tableabove,2,False)+....etc

**GTFORCEMTB** · 06-11-2012, 07:21 PM

I have included the workbook to the thread now if that will help. Thanks.

**tigeravatar** · 06-12-2012, 11:02 AM

GTFORCEMTB,

Attached is a modified version of your posted workbook. On sheet 'Schedule' cell I3 and copied down is this formula:

Please Login or Register  to view this content.

**GTFORCEMTB** · 06-12-2012, 01:38 PM

Thanks tigeravatar that works perfect. I appreciate everyone's time.

Originally Posted by tigeravatar

GTFORCEMTB,

Attached is a modified version of your posted workbook. On sheet 'Schedule' cell I3 and copied down is this formula:

Please Login or Register  to view this content.

**GTFORCEMTB** · 06-12-2012, 06:10 PM

I do have one question how ever say i want to add new shifts how would i go about doing that?

**tigeravatar** · 06-12-2012, 06:13 PM

GTFORCEMTB,

Currently the formula will calculate any shift you type in, although it will have the previously mentioned constraint:

Originally Posted by tigeravatar

Note that if you have a start and end time in the same half of the day (as in, if both were AM or if both were PM, like "3 to 11" or "1 to 9") this formula wouldn't return a correct result.

So if your new shifts will start and end in the same half of the day, either the formula would need to be adjusted, or a different approach used (like the vlookup approach suggested by Richard)