Calculating Shift Lengths

Paul Naylor

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

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

mamady

Board Regular
Joined
Sep 23, 2011
Messages
228
Office Version
  1. 365
Platform
  1. Windows
Hi Paul

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


Regards
Yusuf
 

Paul Naylor

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

mamady

Board Regular
Joined
Sep 23, 2011
Messages
228
Office Version
  1. 365
Platform
  1. Windows
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
 

Paul Naylor

Board Regular
Joined
Sep 2, 2016
Messages
59

ADVERTISEMENT

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: 1

Paul Naylor

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

mamady

Board Regular
Joined
Sep 23, 2011
Messages
228
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Paul,

The time Format should be [h]:mm

Le time know please
 

Attachments

  • 1614851294988.png
    1614851294988.png
    2.1 KB · Views: 1

mamady

Board Regular
Joined
Sep 23, 2011
Messages
228
Office Version
  1. 365
Platform
  1. Windows
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
 

Paul Naylor

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

mamady

Board Regular
Joined
Sep 23, 2011
Messages
228
Office Version
  1. 365
Platform
  1. Windows
I'm glad to help :)

Regards
Yusuf
 

Watch MrExcel Video

Forum statistics

Threads
1,127,422
Messages
5,624,697
Members
416,042
Latest member
Oden

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
Top