Forum Discussion

Snoopydoodle's avatar
Snoopydoodle
Copper Contributor
Mar 02, 2019

Schedule Times to Sum the Hours Scheduled

Is there a way to calculate someone shifts hours scheduled? 

 

Example

An employee's schedule is listed in the first row: A1 lists 2PM-10PM, B1 lists 3PM-11PM; I need C1 to conclude that they are scheduled for 16 hours.

Excel Scheduling Converting Schedules Hours Shifts
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Mar 02, 2019

    Hi,

     

    That could be

    =RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"PM",""),"AM",""),LEN(SUBSTITUTE(SUBSTITUTE(A1,"PM",""),"AM",""))-SEARCH("-",SUBSTITUTE(SUBSTITUTE(A1,"PM",""),"AM","")))-LEFT(SUBSTITUTE(SUBSTITUTE(A1,"PM",""),"AM",""),SEARCH("-",SUBSTITUTE(SUBSTITUTE(A1,"PM",""),"AM",""))-1)+
 RIGHT(SUBSTITUTE(SUBSTITUTE(B1,"PM",""),"AM",""),LEN(SUBSTITUTE(SUBSTITUTE(B1,"PM",""),"AM",""))-SEARCH("-",SUBSTITUTE(SUBSTITUTE(B1,"PM",""),"AM","")))-LEFT(SUBSTITUTE(SUBSTITUTE(B1,"PM",""),"AM",""),SEARCH("-",SUBSTITUTE(SUBSTITUTE(B1,"PM",""),"AM",""))-1)

    if we exclude overnight shifts

     

    • Snoopydoodle's avatar
      Snoopydoodle
      Copper Contributor
      Mar 02, 2019

      Something is not working for me....

       

      The formula I have is:

      =RIGHT(SUBSTITUTE(SUBSTITUTE(B5,"PM",""),"AM",""),LEN(SUBSTITUTE(SUBSTITUTE(B5,"PM",""),"AM",""))-SEARCH("-",SUBSTITUTE(SUBSTITUTE(B5,"PM",""),"AM","")))-LEFT(SUBSTITUTE(SUBSTITUTE(B5,"PM",""),"AM",""),SEARCH("-",SUBSTITUTE(SUBSTITUTE(B5,"PM",""),"AM",""))-1)+RIGHT(SUBSTITUTE(SUBSTITUTE(C5,"PM",""),"AM",""),LEN(SUBSTITUTE(SUBSTITUTE(C5,"PM",""),"AM",""))-SEARCH("-",SUBSTITUTE(SUBSTITUTE(C5,"PM",""),"AM","")))-LEFT(SUBSTITUTE(SUBSTITUTE(C5,"PM",""),"AM",""),SEARCH("-",SUBSTITUTE(SUBSTITUTE(C5,"PM",""),"AM",""))-1)+RIGHT(SUBSTITUTE(SUBSTITUTE(D5,"PM",""),"AM",""),LEN(SUBSTITUTE(SUBSTITUTE(D5,"PM",""),"AM",""))-SEARCH("-",SUBSTITUTE(SUBSTITUTE(D5,"PM",""),"AM","")))-LEFT(SUBSTITUTE(SUBSTITUTE(D5,"PM",""),"AM",""),SEARCH("-",SUBSTITUTE(SUBSTITUTE(D5,"PM",""),"AM",""))-1)+RIGHT(SUBSTITUTE(SUBSTITUTE(E5,"PM",""),"AM",""),LEN(SUBSTITUTE(SUBSTITUTE(E5,"PM",""),"AM",""))-SEARCH("-",SUBSTITUTE(SUBSTITUTE(E5,"PM",""),"AM","")))-LEFT(SUBSTITUTE(SUBSTITUTE(E5,"PM",""),"AM",""),SEARCH("-",SUBSTITUTE(SUBSTITUTE(E5,"PM",""),"AM",""))-1)+ RIGHT(SUBSTITUTE(SUBSTITUTE(F5,"PM",""),"AM",""),LEN(SUBSTITUTE(SUBSTITUTE(F5,"PM",""),"AM",""))-SEARCH("-",SUBSTITUTE(SUBSTITUTE(F5,"PM",""),"AM","")))-LEFT(SUBSTITUTE(SUBSTITUTE(F5,"PM",""),"AM",""),SEARCH("-",SUBSTITUTE(SUBSTITUTE(F5,"PM",""),"AM",""))-1)+RIGHT(SUBSTITUTE(SUBSTITUTE(G5,"PM",""),"AM",""),LEN(SUBSTITUTE(SUBSTITUTE(G5,"PM",""),"AM",""))-SEARCH("-",SUBSTITUTE(SUBSTITUTE(G5,"PM",""),"AM","")))-LEFT(SUBSTITUTE(SUBSTITUTE(G5,"PM",""),"AM",""),SEARCH("-",SUBSTITUTE(SUBSTITUTE(G5,"PM",""),"AM",""))-1)+RIGHT(SUBSTITUTE(SUBSTITUTE(H5,"PM",""),"AM",""),LEN(SUBSTITUTE(SUBSTITUTE(H5,"PM",""),"AM",""))-SEARCH("-",SUBSTITUTE(SUBSTITUTE(H5,"PM",""),"AM","")))-LEFT(SUBSTITUTE(SUBSTITUTE(H5,"PM",""),"AM",""),SEARCH("-",SUBSTITUTE(SUBSTITUTE(H5,"PM",""),"AM",""))-1)

       

      11PM-7AM11PM-7AM12AM-7AM12AM-7AM  10PM - 7AM#VALUE!
      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Mar 05, 2019

        If no empty cells with shifts that could be

        =SUMPRODUCT( MOD(
   (RIGHT(A1:E1,4)<>"12AM")*(MID(A1:E1,SEARCH("-",A1:E1)+1,LEN(A1:E1)-SEARCH("-",A1:E1)-2)+12*(RIGHT(A1:E1,2)="PM"))-
   (LEFT(A1:E1,4)<>"12AM")*(LEFT(A1:E1,SEARCH("-",A1:E1)-3)+12*(MID(A1:E1,SEARCH("-",A1:E1)-2,2)="PM")),24)  )

         

Resources