January 20, 2021

Work time is Monday to Friday 9am to 5pm

In column A I have a list of dates/times to which i need to add 6 hours. If adding 6 hours takes me beyond 5 pm then the additional hours should be added to the next day starting at 9am

Example

Column A Column B

20/01/2021 13:45 20/01/2021 19:45

Adding 6 hours takes me beyond 5pm so I need a formula that would return 21/01/2021 11:45

In addition if the example was on a Friday it should add the additional hours on from Monday as in the example below

Column A Column B

22/01/2021 13:45 25/01/2021 11:45

Thanks for any help

Paul

January 20, 2021

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

January 20, 2021

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

January 20, 2021

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

September 9, 2020

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

1 Guest(s)