Search code examples
excelif-statementconditional-statementsexcel-2013

Chaining Multiple IF Statements in Excel to Calculate Work Shifts from Times

I am trying to calculate a work shift based on the time entered in an Excel worksheet. The shift allocation is as below:

If Time >= 05:30 PM & <= 01:30 AM - 1st shift

If Time >= 01:30 AM & <= 09:30 AM - 2nd shift

If Time >= 09:30 AM & <= 01:30 AM - 3rd shift

I am using IF AND conditions but still it's showing incorrect results. Below is the formula I am using.

=IF(AND(D2>=TIMEVALUE("05:30:00 PM"),(D2<=TIMEVALUE("01:30:00 AM"))),"1",
 IF(AND(D2>=TIMEVALUE("01:30:00 AM"),(D2<=TIMEVALUE("09:30:00 AM"))),"2","3"))

Excel worksheet showing shift times

Any idea what I am doing wrong?

Solution

  • The first problem is in your shift definitions. The time 1:30 AM falls in all three shifts due to the use of less-than-or-equal-to operators. These should be changed to less-than operators, such as the following:

    If Time >= 5:30 PM & < 1:30 AM - 1st shift

    If Time >= 1:30 AM & < 9:30 AM - 2nd shift

    If Time >= 9:30 AM & < 5:30 PM - 3rd shift (note: I corrected an apparent typo here as well)

    The second problem is in the first if statement:

    IF(AND(D2>=TIMEVALUE("05:30:00 PM"),(D2<=TIMEVALUE("01:30:00 AM"))),"1",...)

    The time can't simultaneously be both greater than 5:30:00 PM and less than 01:30:00 AM. Times in Excel are stored as numbers. TIMEVALUE("1:30 AM") returns 0.06250 and TIMEVALUE("5:30 PM") returns approximately 0.72917. As a result this if statement translates to the following:

    IF(AND(D2>=0.72917, D2<=0.06250), "1", ...)

    D2 cannot be both greater-than-or-equal-to 0.72917 and less-than-or-equal-to 0.06250. As a result this will always return false.

    Try rewriting your code as follows:

    =IF(D2<TIMEVALUE("1:30 AM"),"1",
    IF(D2<TIMEVALUE("9:30 AM"),"2",
       IF(D2<TIMEVALUE("5:30 PM"),"3","1")))