Calculating Shift Lengths

P

Paul Naylor

Board Regular
Joined
Sep 2, 2016
Messages
99
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
  2. Mobile
  3. Web
Hi ,

I have staff shifts patterns 3 weekly rotations) in the attached format .

J4,J5 & J6 contain any adjustments e.g. lunch's for weekdays shifts and K4,K5 & K6

Want at the end of each week J4, J5 & J6 on the attached sheet to calculate hours worked for the week. Adjustments are required for unpaid lunches which can vary e.g Sat & Sun its -30mins and Mon-Fri its -45. Can only think to use LEFT & RIGHT to separate and then do the calculations , but that would involve possibly 16 lines. Anyone and idea how to do ?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Hi Paul

There is no attachment. Kindly, can you share the data set using XL2BB?

www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

Regards
Yusuf
 
Upvote 0
mamady said:
Hi Paul

There is no attachment. Kindly, can you share the data set using XL2BB?

www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

Regards
Yusuf
Click to expand...
Hi Yusuf , apologies thought I'd attached, here you go

Kind Regards,

Paul
 

Attachments

  • Screenshot_20210226-220020_Excel.jpg
    Screenshot_20210226-220020_Excel.jpg
    160.1 KB · Views: 13
Upvote 0
Paul Naylor said:
Hi Yusuf , apologies thought I'd attached, here you go

Kind Regards,

Paul
Click to expand...

Hi Paul,

I am not sure if I understood your requirement correctly, please check the below

work shift.xlsx
BCDEFGHIJ
2Sam Smith ( 263076 )
3Wk No.SunMonTueWedThuFriSatHours Worked
4Wk 1RDO9.00 - 17.159.00 - 17.159.00 - 17.159.00 - 17.159.00 - 17.15RDO41:15
5Wk 2RDO12.00 -20.0012.00 -20.00RDO12.00 -20.0012.00 -20.009.00 -18.0041:00
6Wk 310:00 - 17:008.00 - 16.30RDO8.00 - 16.308.00 - 16.308.00 - 16.30RDO41:00
Sheet1
Cell Formulas
RangeFormula
J4:J6J4=SUM(IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),SEARCH("-",SUBSTITUTE(C4,".",":"))+1,LEN(SUBSTITUTE(C4,".",":"))-SEARCH("-",SUBSTITUTE(C4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),1,SEARCH("-",SUBSTITUTE(C4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),SEARCH("-",SUBSTITUTE(D4,".",":"))+1,LEN(SUBSTITUTE(D4,".",":"))-SEARCH("-",SUBSTITUTE(D4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),1,SEARCH("-",SUBSTITUTE(D4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),SEARCH("-",SUBSTITUTE(E4,".",":"))+1,LEN(SUBSTITUTE(E4,".",":"))-SEARCH("-",SUBSTITUTE(E4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),1,SEARCH("-",SUBSTITUTE(E4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),SEARCH("-",SUBSTITUTE(F4,".",":"))+1,LEN(SUBSTITUTE(F4,".",":"))-SEARCH("-",SUBSTITUTE(F4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),1,SEARCH("-",SUBSTITUTE(F4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),SEARCH("-",SUBSTITUTE(G4,".",":"))+1,LEN(SUBSTITUTE(G4,".",":"))-SEARCH("-",SUBSTITUTE(G4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),1,SEARCH("-",SUBSTITUTE(G4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),SEARCH("-",SUBSTITUTE(H4,".",":"))+1,LEN(SUBSTITUTE(H4,".",":"))-SEARCH("-",SUBSTITUTE(H4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),1,SEARCH("-",SUBSTITUTE(H4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),SEARCH("-",SUBSTITUTE(I4,".",":"))+1,LEN(SUBSTITUTE(I4,".",":"))-SEARCH("-",SUBSTITUTE(I4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),1,SEARCH("-",SUBSTITUTE(I4,".",":"))-1))*1,0))


I am sure that such a long formula can be simplified if the data format is corrected/changed, if you had Office 365, if had a better understanding of your requirement.

cheers

Best Regards
Yusuf
 
Upvote 0
mamady said:
Hi Paul,

I am not sure if I understood your requirement correctly, please check the below

work shift.xlsx
BCDEFGHIJ
2Sam Smith ( 263076 )
3Wk No.SunMonTueWedThuFriSatHours Worked
4Wk 1RDO9.00 - 17.159.00 - 17.159.00 - 17.159.00 - 17.159.00 - 17.15RDO41:15
5Wk 2RDO12.00 -20.0012.00 -20.00RDO12.00 -20.0012.00 -20.009.00 -18.0041:00
6Wk 310:00 - 17:008.00 - 16.30RDO8.00 - 16.308.00 - 16.308.00 - 16.30RDO41:00
Sheet1
Cell Formulas
RangeFormula
J4:J6J4=SUM(IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),SEARCH("-",SUBSTITUTE(C4,".",":"))+1,LEN(SUBSTITUTE(C4,".",":"))-SEARCH("-",SUBSTITUTE(C4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),1,SEARCH("-",SUBSTITUTE(C4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),SEARCH("-",SUBSTITUTE(D4,".",":"))+1,LEN(SUBSTITUTE(D4,".",":"))-SEARCH("-",SUBSTITUTE(D4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),1,SEARCH("-",SUBSTITUTE(D4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),SEARCH("-",SUBSTITUTE(E4,".",":"))+1,LEN(SUBSTITUTE(E4,".",":"))-SEARCH("-",SUBSTITUTE(E4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),1,SEARCH("-",SUBSTITUTE(E4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),SEARCH("-",SUBSTITUTE(F4,".",":"))+1,LEN(SUBSTITUTE(F4,".",":"))-SEARCH("-",SUBSTITUTE(F4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),1,SEARCH("-",SUBSTITUTE(F4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),SEARCH("-",SUBSTITUTE(G4,".",":"))+1,LEN(SUBSTITUTE(G4,".",":"))-SEARCH("-",SUBSTITUTE(G4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),1,SEARCH("-",SUBSTITUTE(G4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),SEARCH("-",SUBSTITUTE(H4,".",":"))+1,LEN(SUBSTITUTE(H4,".",":"))-SEARCH("-",SUBSTITUTE(H4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),1,SEARCH("-",SUBSTITUTE(H4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),SEARCH("-",SUBSTITUTE(I4,".",":"))+1,LEN(SUBSTITUTE(I4,".",":"))-SEARCH("-",SUBSTITUTE(I4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),1,SEARCH("-",SUBSTITUTE(I4,".",":"))-1))*1,0))


I am sure that such a long formula can be simplified if the data format is corrected/changed, if you had Office 365, if had a better understanding of your requirement.

cheers

Best Regards
Yusuf
Click to expand...
Hi Yusuf , Thanks for taking the time to reply , i've copied formula into my sheet ( Column K) and getting a different result unsure if I've missed some of the formula out or its just a formatting issue? ( uploaded sheet) You'll see that I've added another column (which is a weekly deduction I need to make ( unpaid Lunch) which should if my figures are right give 37.5 hrs per week . formula below and screen shot of results . cells C4-I4 . Formatting is all hh.mm .

=SUM(IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),SEARCH("-",SUBSTITUTE(C4,".",":"))+1,LEN(SUBSTITUTE(C4,".",":"))-SEARCH("-",SUBSTITUTE(C4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),1,SEARCH("-",SUBSTITUTE(C4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),SEARCH("-",SUBSTITUTE(D4,".",":"))+1,LEN(SUBSTITUTE(D4,".",":"))-SEARCH("-",SUBSTITUTE(D4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),1,SEARCH("-",SUBSTITUTE(D4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),SEARCH("-",SUBSTITUTE(E4,".",":"))+1,LEN(SUBSTITUTE(E4,".",":"))-SEARCH("-",SUBSTITUTE(E4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),1,SEARCH("-",SUBSTITUTE(E4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),SEARCH("-",SUBSTITUTE(F4,".",":"))+1,LEN(SUBSTITUTE(F4,".",":"))-SEARCH("-",SUBSTITUTE(F4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),1,SEARCH("-",SUBSTITUTE(F4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),SEARCH("-",SUBSTITUTE(G4,".",":"))+1,LEN(SUBSTITUTE(G4,".",":"))-SEARCH("-",SUBSTITUTE(G4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),1,SEARCH("-",SUBSTITUTE(G4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),SEARCH("-",SUBSTITUTE(H4,".",":"))+1,LEN(SUBSTITUTE(H4,".",":"))-SEARCH("-",SUBSTITUTE(H4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),1,SEARCH("-",SUBSTITUTE(H4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),SEARCH("-",SUBSTITUTE(I4,".",":"))+1,LEN(SUBSTITUTE(I4,".",":"))-SEARCH("-",SUBSTITUTE(I4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),1,SEARCH("-",SUBSTITUTE(I4,".",":"))-1))*1,0))
 

Attachments

  • Shift Length Calculation.JPG
    Shift Length Calculation.JPG
    81.2 KB · Views: 4
Upvote 0
mamady said:
Hi Paul,

I am not sure if I understood your requirement correctly, please check the below

work shift.xlsx
BCDEFGHIJ
2Sam Smith ( 263076 )
3Wk No.SunMonTueWedThuFriSatHours Worked
4Wk 1RDO9.00 - 17.159.00 - 17.159.00 - 17.159.00 - 17.159.00 - 17.15RDO41:15
5Wk 2RDO12.00 -20.0012.00 -20.00RDO12.00 -20.0012.00 -20.009.00 -18.0041:00
6Wk 310:00 - 17:008.00 - 16.30RDO8.00 - 16.308.00 - 16.308.00 - 16.30RDO41:00
Sheet1
Cell Formulas
RangeFormula
J4:J6J4=SUM(IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),SEARCH("-",SUBSTITUTE(C4,".",":"))+1,LEN(SUBSTITUTE(C4,".",":"))-SEARCH("-",SUBSTITUTE(C4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),1,SEARCH("-",SUBSTITUTE(C4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),SEARCH("-",SUBSTITUTE(D4,".",":"))+1,LEN(SUBSTITUTE(D4,".",":"))-SEARCH("-",SUBSTITUTE(D4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),1,SEARCH("-",SUBSTITUTE(D4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),SEARCH("-",SUBSTITUTE(E4,".",":"))+1,LEN(SUBSTITUTE(E4,".",":"))-SEARCH("-",SUBSTITUTE(E4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),1,SEARCH("-",SUBSTITUTE(E4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),SEARCH("-",SUBSTITUTE(F4,".",":"))+1,LEN(SUBSTITUTE(F4,".",":"))-SEARCH("-",SUBSTITUTE(F4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),1,SEARCH("-",SUBSTITUTE(F4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),SEARCH("-",SUBSTITUTE(G4,".",":"))+1,LEN(SUBSTITUTE(G4,".",":"))-SEARCH("-",SUBSTITUTE(G4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),1,SEARCH("-",SUBSTITUTE(G4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),SEARCH("-",SUBSTITUTE(H4,".",":"))+1,LEN(SUBSTITUTE(H4,".",":"))-SEARCH("-",SUBSTITUTE(H4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),1,SEARCH("-",SUBSTITUTE(H4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),SEARCH("-",SUBSTITUTE(I4,".",":"))+1,LEN(SUBSTITUTE(I4,".",":"))-SEARCH("-",SUBSTITUTE(I4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),1,SEARCH("-",SUBSTITUTE(I4,".",":"))-1))*1,0))


I am sure that such a long formula can be simplified if the data format is corrected/changed, if you had Office 365, if had a better understanding of your requirement.

cheers

Best Regards
Yusuf
Click to expand...
Hi Yusuf, was using excel 97 on wks PC , but using Excel 2018 at home , which is what the attached results are on , but if necessary I upload to office 365 and work with it their if a later version is what is needed?
 
Upvote 0
Hi Paul,

The time Format should be [h]:mm

Le time know please
 

Attachments

  • 1614851294988.png
    1614851294988.png
    2.1 KB · Views: 4
Upvote 0
I have updated the formula to deduct column J (Deductions):

work shift.xlsx
BCDEFGHIJK
2Sam Smith ( 263076 )
3Wk No.SunMonTueWedThuFriSatDeductionHours Worked
4Wk 1RDO9.00 - 17.159.00 - 17.159.00 - 17.159.00 - 17.159.00 - 17.15RDO3.4537:30
5Wk 2RDO12.00 -20.0012.00 -20.00RDO12.00 -20.0012.00 -20.009.00 -18.003.3037:57
6Wk 310:00 - 17:008.00 - 16.30RDO8.00 - 16.308.00 - 16.308.00 - 16.30RDO3.3037:57
Sheet1
Cell Formulas
RangeFormula
K4:K6K4=SUM(IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),SEARCH("-",SUBSTITUTE(C4,".",":"))+1,LEN(SUBSTITUTE(C4,".",":"))-SEARCH("-",SUBSTITUTE(C4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),1,SEARCH("-",SUBSTITUTE(C4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),SEARCH("-",SUBSTITUTE(D4,".",":"))+1,LEN(SUBSTITUTE(D4,".",":"))-SEARCH("-",SUBSTITUTE(D4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),1,SEARCH("-",SUBSTITUTE(D4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),SEARCH("-",SUBSTITUTE(E4,".",":"))+1,LEN(SUBSTITUTE(E4,".",":"))-SEARCH("-",SUBSTITUTE(E4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),1,SEARCH("-",SUBSTITUTE(E4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),SEARCH("-",SUBSTITUTE(F4,".",":"))+1,LEN(SUBSTITUTE(F4,".",":"))-SEARCH("-",SUBSTITUTE(F4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),1,SEARCH("-",SUBSTITUTE(F4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),SEARCH("-",SUBSTITUTE(G4,".",":"))+1,LEN(SUBSTITUTE(G4,".",":"))-SEARCH("-",SUBSTITUTE(G4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),1,SEARCH("-",SUBSTITUTE(G4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),SEARCH("-",SUBSTITUTE(H4,".",":"))+1,LEN(SUBSTITUTE(H4,".",":"))-SEARCH("-",SUBSTITUTE(H4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),1,SEARCH("-",SUBSTITUTE(H4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),SEARCH("-",SUBSTITUTE(I4,".",":"))+1,LEN(SUBSTITUTE(I4,".",":"))-SEARCH("-",SUBSTITUTE(I4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),1,SEARCH("-",SUBSTITUTE(I4,".",":"))-1))*1,0))-SUBSTITUTE(J4,".",":")*1


The time Format should be [h]:mm

Regards
 
Upvote 0
mamady said:
I have updated the formula to deduct column J (Deductions):

work shift.xlsx
BCDEFGHIJK
2Sam Smith ( 263076 )
3Wk No.SunMonTueWedThuFriSatDeductionHours Worked
4Wk 1RDO9.00 - 17.159.00 - 17.159.00 - 17.159.00 - 17.159.00 - 17.15RDO3.4537:30
5Wk 2RDO12.00 -20.0012.00 -20.00RDO12.00 -20.0012.00 -20.009.00 -18.003.3037:57
6Wk 310:00 - 17:008.00 - 16.30RDO8.00 - 16.308.00 - 16.308.00 - 16.30RDO3.3037:57
Sheet1
Cell Formulas
RangeFormula
K4:K6K4=SUM(IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),SEARCH("-",SUBSTITUTE(C4,".",":"))+1,LEN(SUBSTITUTE(C4,".",":"))-SEARCH("-",SUBSTITUTE(C4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(C4,".",":"),1,SEARCH("-",SUBSTITUTE(C4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),SEARCH("-",SUBSTITUTE(D4,".",":"))+1,LEN(SUBSTITUTE(D4,".",":"))-SEARCH("-",SUBSTITUTE(D4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(D4,".",":"),1,SEARCH("-",SUBSTITUTE(D4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),SEARCH("-",SUBSTITUTE(E4,".",":"))+1,LEN(SUBSTITUTE(E4,".",":"))-SEARCH("-",SUBSTITUTE(E4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(E4,".",":"),1,SEARCH("-",SUBSTITUTE(E4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),SEARCH("-",SUBSTITUTE(F4,".",":"))+1,LEN(SUBSTITUTE(F4,".",":"))-SEARCH("-",SUBSTITUTE(F4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(F4,".",":"),1,SEARCH("-",SUBSTITUTE(F4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),SEARCH("-",SUBSTITUTE(G4,".",":"))+1,LEN(SUBSTITUTE(G4,".",":"))-SEARCH("-",SUBSTITUTE(G4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(G4,".",":"),1,SEARCH("-",SUBSTITUTE(G4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),SEARCH("-",SUBSTITUTE(H4,".",":"))+1,LEN(SUBSTITUTE(H4,".",":"))-SEARCH("-",SUBSTITUTE(H4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(H4,".",":"),1,SEARCH("-",SUBSTITUTE(H4,".",":"))-1))*1,0),IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),SEARCH("-",SUBSTITUTE(I4,".",":"))+1,LEN(SUBSTITUTE(I4,".",":"))-SEARCH("-",SUBSTITUTE(I4,".",":"))+1))*1,0)-IFERROR(TRIM(MID(SUBSTITUTE(I4,".",":"),1,SEARCH("-",SUBSTITUTE(I4,".",":"))-1))*1,0))-SUBSTITUTE(J4,".",":")*1


The time Format should be [h]:mm

Regards
Click to expand...
Hi Malady,

That's great works a treat, better than my previous solution . Extract via LEFT & RIGHT function 14 results and 2 formulas per line as opposed to adjustment column and formula.

Thanks so much

Regards,

Paul
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula to "Review" Alternating Shift Schedules
Replies
0
Views
143
davidcox9
D
K
  • Question Question
Production Time Calculator
Replies
4
Views
471
Drrellik
Drrellik
U
  • Solved
Newbie Seeking Help with Ranges, Loops, and Select
2 3
Replies
27
Views
1K
USNA91
U
T
  • Question Question
List of unused staff
Replies
3
Views
264
FormR
FormR
L
  • Question Question
How to Repeat shifts and have it auto fill after giving first line.
Replies
2
Views
297
LPHBELLO
L

Forum statistics

Threads
1,224,616
Messages
6,179,908
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top