I have been working with Bill from Montana on how to easily run a report calculating which shifts certain injuries occurred on.

As you may know, I love an Excel challenge, and so, Bill and I have been exchanging emails back and forth as we got closer and closer to an elegant solution.

Key to this is getting Excel to recognise times as times.  By using a feature known as Text to Columns, that is now really easy.

The other trick here is using nested IF formulas which although it looks really smart and complicated is actally rather simple.  If you watch the video above (I would encourage you to double click and open it in YouTube so you can watch it in maximum resolution) you will see how to do it.

The formula is basically saying this:

  • If the time is before 7:00am say “3rd Shift”
    • Otherwise: If the time is before 3:00pm say “1st Shift”
      • Otherwise: If the time is before 11:00pm say “2nd Shift”
        • Otherwise: If the time is before midnight say “3rd Shift”
          • Otherwise: it can’t be a time so say “No Time”

Enjoy

Let me know what you think or other uses you can put this to.

:: Justin ::