Calculating Shift Lengths

Paul Naylor

Board Regular
Joined
Sep 2, 2016
Messages
98
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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi Paul

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


Regards
Yusuf
 
Upvote 0
Hi Paul

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


Regards
Yusuf
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: 12
Upvote 0
Hi Yusuf , apologies thought I'd attached, here you go

Kind Regards,

Paul

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
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
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: 3
Upvote 0
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
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: 3
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
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
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

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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