Forum Discussion

frans hendriks's avatar
frans hendriks
Copper Contributor
Apr 20, 2017
Solved

Time to periode

Hello, 

 

I have a sheet white data from calls, 

I want tot sort/change that in to "periods"  like 07:00  to 12:00 is morning, 13:00 to 18:00 afternoon and 19:00 tot 07:00 is night. 

 

 

Is there a formula to do that?

 

Tnx  for helping

 

Frans

 

 

Formulas & Functions
Show and Tell
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 20, 2017

    Hi France,

     

    You may use nested IF. To simplify, I added your periods to the as here

     

    and after that use formula in next to time columns

    =IF(AND(B2>=$E$2,B2<=$E$3),$F$2,
      IF(AND(B2>=$E$4,B2<=$E$5),$F$4,
      IF(OR(B2>=$E$6,B2<=$E$7),$F$6,
          "Break")
      )
)

    See second sheet in the attached file. Periods you didn't define (e.g. between 12:00 and 13:00) are Break here.

     

     

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 20, 2017

    Hi France,

     

    You may use nested IF. To simplify, I added your periods to the as here

     

    and after that use formula in next to time columns

    =IF(AND(B2>=$E$2,B2<=$E$3),$F$2,
      IF(AND(B2>=$E$4,B2<=$E$5),$F$4,
      IF(OR(B2>=$E$6,B2<=$E$7),$F$6,
          "Break")
      )
)

    See second sheet in the attached file. Periods you didn't define (e.g. between 12:00 and 13:00) are Break here.