Log In
Register
Home
January 20, 2021
Offline
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
September 9, 2020
Offline
Hi Paul,
Here is a solution with a formula.
BR,
Lionel
January 20, 2021
Offline
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
September 9, 2020
Offline
Paul,
A new version.
BR,
Lionel
January 20, 2021
Offline
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
September 9, 2020
Offline
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
January 20, 2021
Offline
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
January 20, 2021
Offline
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
September 9, 2020
Offline
Hi Paul
Here is the adaptation for the WE. I added a column E (in green) with the new formula.
BR,
Lionel
January 20, 2021
Offline
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
Offline
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
January 20, 2021
Offline
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
1 Guest(s)
