Hi Paul,

Here is a solution with a formula. 

BR,

Lionel

Hi Lionel

That is great thank you

Is it possible to adapt that formula so that if the start time is out of work hours (between 17:01 & 08:59) the 6 hours is added on from 09:00, so always giving the answer 15:00

Thanks in advance for any help

Paul 

Paul,

A new version.

BR,

Lionel

Hi Lionel

Thank you that is perfect

I struggle with time calculations in excel, so have been looking at your formula and whilst I could not have found that solution, I have looked at it and mostly understand what it is doing. Which I find useful as I may have another application in the future I can adapt this for

I have one question, as I do not understand why you have used 360/60/24 and not just 6/24 to get the 6 hour gain?

Thanks again

Paul

Paul,

For the 360/60/24, there is no reason. It's just that at certain times the brain does certain things automatically.
You are absolutely right that we can also use 6/24.

BR,

Lionel

Ok thanks Lionel, just checking there wasn’t a reason for your doing that way. 
Thanks again for the solution, it will save many hours of work every month and improve accuracy

Paul

Hi Lionel

I have just put the formula in my spreadsheet, back to the start of the year and found a problem with entries that are on a Saturday and Sunday, which all should return an answer which is Monday 15:00

Like Friday it should add the additional hours on from Monday as in the example below

Column A             Column B
16/01/2021 13:45 18/01/2021 15:00

17/01/2021 11:15  18/01/2021 15:00

All entries between 17:00 Friday and 08:00 Monday should return an answer which is Monday 15:00

I have added a second sheet with the problems highlighted yellow in your spreadsheet attached

Is it possible to fix this?

Thanks

Paul

Hi Paul

Here is the adaptation for the WE. I added a column E (in green) with the new formula.

BR,

Lionel

Hi Lionel

Once again many thanks for your help

I have used your new formula with my data, back to the beginning of the year, it was all ok until I realised that Friday 1st January was a holiday and thus not a work day

I researched the WORKDAY.INTL function in the formula and found I could add the holiday dates into the formula, which I did but did not get the expected result

On sheet 2 of the attached spreadsheet the formula I used is in column G (blue) and the holidays listed in column J. The results that I think are wrong are highlighted yellow with the expected results next to them in column H

I think all entries between 31/12/2021 17:00 and 04/01/2021 09:00 should return an answer which is 04/01/2021 15:00

I have added a third sheet covering 01/04 to 08/04 to see what the results looked like over the weekend as both Friday 02/04 and Monday 05/04 are holidays, again there were unexpected results. The results that I think are wrong are highlighted yellow with the expected results next to them in column H

Is it possible to fix this

Also on your formula below, if the target was changed from 6 hours to:

5 hours, would I just need to change 60 to 72 and 15/24 to 14/24

4 hours, would I just need to change 60 to 90 and 15/24 to 13/24

=IF(WEEKDAY(B2,2)>5,WORKDAY.INTL((INT(B2)+17/24),1,1)+15/24,IF(B2<(INT(B2)+9/24),(INT(B2)+15/24),IF(B2>(INT(B2)+17/24),WORKDAY.INTL((INT(B2)+17/24),1,1)+15/24,IF((B2+360/60/24)>(INT(B2)+17/24),WORKDAY.INTL((INT(B2)+17/24),1,1)+9/24+MOD((B2+360/60/24)-(INT(B2)+17/24),1),B2+360/60/24))))

Thanks

Paul

Hi Paul,

Here is an adaptation of the file taking into account the public holidays (which are on D2:D9). I have added an M column with the new results (which seem to be correct) on a new sheet (4).

You will see, the formula has been modified very slightly. This concerns the first condition where I check that the date is either a weekend day or a public holiday.

Still a little closer to perfection 😉

BR,

Lionel

Hi Lionel

Thanks for your patience in sorting this out for me

I have put your latest formula into my spreadsheet to the beginning of the year and everything looks good and hopefully will continue to

Thanks again

Paul