Mar 02 2019 07:53 AM
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.
Mar 02 2019 08:07 AM
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
Mar 02 2019 09:39 AM
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-7AM | 11PM-7AM | 12AM-7AM | 12AM-7AM | 10PM - 7AM | #VALUE! |
Mar 04 2019 07:49 PM
Mar 05 2019 03:12 AM
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) )