Forum Discussion

Gal1971's avatar
Gal1971
Copper Contributor
Jul 25, 2020
Solved

how do I group three time ranges into shifts in Excel?

Hello all, anyone know how to group three different time ranges into morning/ evening/ night (or 1/2/3) shifts Excel 2016? thanks!

  • Gal1971 

     

    The problem was two-fold at least.

    1. The column in which you were displaying "Visit Time" actually contained DATE and TIME (I've changed the format only in the attached to make that visible; I did not change the values you had in those cells).
    2. So that meant we had to extract from that (Date & Time) field the Time only, which I've done in a new column.
    3. And I then formatted both that column and the table's column to read with the standard Time format. I also put in midnight as part of the night shift, for the VLOOKUP fallback when the time of the visit is in the wee-small-hours of the morning.

     

    So I think this now works.......

11 Replies

  • Ranga_Kumara's avatar
    Ranga_Kumara
    Copper Contributor

    Hi, I would appreciate if you can help me why Vlookup is not working here. Kindly help me on this.

     

    • mathetes's avatar
      mathetes
      Silver Contributor
      Please start a new thread on your question rather than tacking on to a two year old thread. In the process, please give a bit more information. For example, what are the references in your VLOOKUP formula? I was able to detect that the formula itself is appearing in cell F2 (and copied down), but you use named ranges without telling us where those are.
      • Ranga_Kumara's avatar
        Ranga_Kumara
        Copper Contributor

        mathetes Thanks for your prompt reply.

         

        Name range - H2:I16

         

        Daily from 9pm to 6am in the following day (H column) the security has to punch in 3 locations. They have -15 to 15 minutes grace period as in J and K column. I have prepared buckets based on the min and max timing.

        Patrol time (B column) has the date and timing. So I take the timing and check whether timing is within the bucket through vlookup.

         

        Thanks

        Rgds

    • Gal1971's avatar
      Gal1971
      Copper Contributor

      Thanks mathetes !

      copied it to my table, but it is not working - I get the same number 3 for all shifts. I think the problem is that my actual time column is not numbers as in yours - but in Excel date/time format. Guess it matters?

      • mathetes's avatar
        mathetes
        Silver Contributor

        Gal1971 

         

        I think mine were in Excel time format... but either way, Yes, it matters.

         

        Can you post your actual sheet, just so long as it doesn't have real people data on it......There may be something else going on with the VLOOKUP.

Resources