Home

Schedule Times to Sum the Hours Scheduled

%3CLINGO-SUB%20id%3D%22lingo-sub-359381%22%20slang%3D%22en-US%22%3ESchedule%20Times%20to%20Sum%20the%20Hours%20Scheduled%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359381%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20calculate%20someone%20shifts%20hours%20scheduled%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EExample%3C%2FEM%3E%3C%2FP%3E%3CP%3EAn%20employee's%20schedule%20is%20listed%20in%20the%20first%20row%3A%20A1%20lists%202PM-10PM%2C%20B1%20lists%203PM-11PM%3B%20I%20need%20C1%20to%20conclude%20that%20they%20are%20scheduled%20for%2016%20hours.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-359381%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Scheduling%20Converting%20Schedules%20Hours%20Shifts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-360320%22%20slang%3D%22en-US%22%3ERe%3A%20Schedule%20Times%20to%20Sum%20the%20Hours%20Scheduled%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-360320%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20no%20empty%20cells%20with%20shifts%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(%20MOD(%0A%20%20%20(RIGHT(A1%3AE1%2C4)%26lt%3B%26gt%3B%2212AM%22)*(MID(A1%3AE1%2CSEARCH(%22-%22%2CA1%3AE1)%2B1%2CLEN(A1%3AE1)-SEARCH(%22-%22%2CA1%3AE1)-2)%2B12*(RIGHT(A1%3AE1%2C2)%3D%22PM%22))-%0A%20%20%20(LEFT(A1%3AE1%2C4)%26lt%3B%26gt%3B%2212AM%22)*(LEFT(A1%3AE1%2CSEARCH(%22-%22%2CA1%3AE1)-3)%2B12*(MID(A1%3AE1%2CSEARCH(%22-%22%2CA1%3AE1)-2%2C2)%3D%22PM%22))%2C24)%20%20)%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20653px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F85096i227957D776A87D0C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-360199%22%20slang%3D%22en-US%22%3ERe%3A%20Schedule%20Times%20to%20Sum%20the%20Hours%20Scheduled%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-360199%22%20slang%3D%22en-US%22%3EFor%20any%20shift%20up%20to%20a%20maximum%20of%2012%20hours%2C%20this%20formula%20in%20C2%20returns%20your%20desired%20result%3A%3CBR%20%2F%3E%3DIF(MAX(INDEX(IFERROR(--MID(A1%2CFIND(%22-%22%2CA1)%2B1%2C%7B1%2C2%7D)%2C%22%22)%2C0))-%3CBR%20%2F%3EMAX(INDEX(IFERROR(--LEFT(A1%2C%7B1%2C2%7D)%2C%22%22)%2C0))%26lt%3B1%2C%3CBR%20%2F%3EMAX(INDEX(IFERROR(--MID(A1%2CFIND(%22-%22%2CA1)%2B1%2C%7B1%2C2%7D)%2C%22%22)%2C0))-%3CBR%20%2F%3EMAX(INDEX(IFERROR(--LEFT(A1%2C%7B1%2C2%7D)%2C%22%22)%2C0))%2B12%2C%3CBR%20%2F%3EMAX(INDEX(IFERROR(--MID(A1%2CFIND(%22-%22%2CA1)%2B1%2C%7B1%2C2%7D)%2C%22%22)%2C0))-%3CBR%20%2F%3EMAX(INDEX(IFERROR(--LEFT(A1%2C%7B1%2C2%7D)%2C%22%22)%2C0)))%2B%3CBR%20%2F%3EIF(MAX(INDEX(IFERROR(--MID(B1%2CFIND(%22-%22%2CB1)%2B1%2C%7B1%2C2%7D)%2C%22%22)%2C0))-%3CBR%20%2F%3EMAX(INDEX(IFERROR(--LEFT(B1%2C%7B1%2C2%7D)%2C%22%22)%2C0))%26lt%3B1%2C%3CBR%20%2F%3EMAX(INDEX(IFERROR(--MID(B1%2CFIND(%22-%22%2CB1)%2B1%2C%7B1%2C2%7D)%2C%22%22)%2C0))-%3CBR%20%2F%3EMAX(INDEX(IFERROR(--LEFT(B1%2C%7B1%2C2%7D)%2C%22%22)%2C0))%2B12%2C%3CBR%20%2F%3EMAX(INDEX(IFERROR(--MID(B1%2CFIND(%22-%22%2CB1)%2B1%2C%7B1%2C2%7D)%2C%22%22)%2C0))-%3CBR%20%2F%3EMAX(INDEX(IFERROR(--LEFT(B1%2C%7B1%2C2%7D)%2C%22%22)%2C0)))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359392%22%20slang%3D%22en-US%22%3ERe%3A%20Schedule%20Times%20to%20Sum%20the%20Hours%20Scheduled%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359392%22%20slang%3D%22en-US%22%3E%3CP%3ESomething%20is%20not%20working%20for%20me....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I%20have%20is%3A%3C%2FP%3E%3CP%3E%3DRIGHT(SUBSTITUTE(SUBSTITUTE(B5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CLEN(SUBSTITUTE(SUBSTITUTE(B5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-SEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(B5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)))-LEFT(SUBSTITUTE(SUBSTITUTE(B5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CSEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(B5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-1)%2BRIGHT(SUBSTITUTE(SUBSTITUTE(C5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CLEN(SUBSTITUTE(SUBSTITUTE(C5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-SEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(C5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)))-LEFT(SUBSTITUTE(SUBSTITUTE(C5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CSEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(C5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-1)%2BRIGHT(SUBSTITUTE(SUBSTITUTE(D5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CLEN(SUBSTITUTE(SUBSTITUTE(D5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-SEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(D5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)))-LEFT(SUBSTITUTE(SUBSTITUTE(D5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CSEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(D5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-1)%2BRIGHT(SUBSTITUTE(SUBSTITUTE(E5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CLEN(SUBSTITUTE(SUBSTITUTE(E5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-SEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(E5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)))-LEFT(SUBSTITUTE(SUBSTITUTE(E5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CSEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(E5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-1)%2B%20RIGHT(SUBSTITUTE(SUBSTITUTE(F5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CLEN(SUBSTITUTE(SUBSTITUTE(F5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-SEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(F5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)))-LEFT(SUBSTITUTE(SUBSTITUTE(F5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CSEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(F5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-1)%2BRIGHT(SUBSTITUTE(SUBSTITUTE(G5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CLEN(SUBSTITUTE(SUBSTITUTE(G5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-SEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(G5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)))-LEFT(SUBSTITUTE(SUBSTITUTE(G5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CSEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(G5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-1)%2BRIGHT(SUBSTITUTE(SUBSTITUTE(H5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CLEN(SUBSTITUTE(SUBSTITUTE(H5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-SEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(H5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)))-LEFT(SUBSTITUTE(SUBSTITUTE(H5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CSEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(H5%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E11PM-7AM%3C%2FTD%3E%3CTD%3E11PM-7AM%3C%2FTD%3E%3CTD%3E12AM-7AM%3C%2FTD%3E%3CTD%3E12AM-7AM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E10PM%20-%207AM%3C%2FTD%3E%3CTD%3E%23VALUE!%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359382%22%20slang%3D%22en-US%22%3ERe%3A%20Schedule%20Times%20to%20Sum%20the%20Hours%20Scheduled%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359382%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DRIGHT(SUBSTITUTE(SUBSTITUTE(A1%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CLEN(SUBSTITUTE(SUBSTITUTE(A1%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-SEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(A1%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)))-LEFT(SUBSTITUTE(SUBSTITUTE(A1%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CSEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(A1%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-1)%2B%0A%20RIGHT(SUBSTITUTE(SUBSTITUTE(B1%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CLEN(SUBSTITUTE(SUBSTITUTE(B1%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-SEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(B1%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)))-LEFT(SUBSTITUTE(SUBSTITUTE(B1%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22)%2CSEARCH(%22-%22%2CSUBSTITUTE(SUBSTITUTE(B1%2C%22PM%22%2C%22%22)%2C%22AM%22%2C%22%22))-1)%3C%2FPRE%3E%0A%3CP%3Eif%20we%20exclude%20overnight%20shifts%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Snoopydoodle
New Contributor

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.

4 Replies

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

 

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!
For any shift up to a maximum of 12 hours, this formula in C2 returns your desired result:
=IF(MAX(INDEX(IFERROR(--MID(A1,FIND("-",A1)+1,{1,2}),""),0))-
MAX(INDEX(IFERROR(--LEFT(A1,{1,2}),""),0))<1,
MAX(INDEX(IFERROR(--MID(A1,FIND("-",A1)+1,{1,2}),""),0))-
MAX(INDEX(IFERROR(--LEFT(A1,{1,2}),""),0))+12,
MAX(INDEX(IFERROR(--MID(A1,FIND("-",A1)+1,{1,2}),""),0))-
MAX(INDEX(IFERROR(--LEFT(A1,{1,2}),""),0)))+
IF(MAX(INDEX(IFERROR(--MID(B1,FIND("-",B1)+1,{1,2}),""),0))-
MAX(INDEX(IFERROR(--LEFT(B1,{1,2}),""),0))<1,
MAX(INDEX(IFERROR(--MID(B1,FIND("-",B1)+1,{1,2}),""),0))-
MAX(INDEX(IFERROR(--LEFT(B1,{1,2}),""),0))+12,
MAX(INDEX(IFERROR(--MID(B1,FIND("-",B1)+1,{1,2}),""),0))-
MAX(INDEX(IFERROR(--LEFT(B1,{1,2}),""),0)))

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)  )

image.png

 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies