• 460K Members
• 10.4K Online
• 557K Conversations

New Contributor

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.

4 Replies

Re: Schedule Times to Sum the Hours Scheduled

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

Re: Schedule Times to Sum the Hours Scheduled

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!

Re: Schedule Times to Sum the Hours Scheduled

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

Re: Schedule Times to Sum the Hours Scheduled

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies