I'm trying to do a calculation where time can be added but only adds within working hours & days. I found this calculation: =WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$G$2,1,0),$H$2:$H$14)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$G$2,$F$2+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$G$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)) https://answers.microsoft.com/en-us/msoffice/forum/all/add-work-hours-to-a-date-and-time/c41cf049-3797-48a3-8156-438401c6137c But the issue is it doesn't correctly pick up the start time & if you time stamp outside the working hours it also doesn't calculate correctly. For example if start time is 07:00 it adds 2 hours from that time when it should add 2 hours from 09:00 as the start time. thanks,

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Adding time to date & time but fit within working days & hours

Thread starter Alisrune
Start date Sep 25, 2019
Tags

calculation hours start time working

Alisrune

New Member

Sep 25, 2019

I'm trying to do a calculation where time can be added but only adds within working hours & days. I found this calculation:

[FONT=&quot]=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$G$2,1,0),$H$2:$H$14)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$G$2,$F$2+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$G$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0))[/FONT]

https://answers.microsoft.com/en-us...and-time/c41cf049-3797-48a3-8156-438401c6137c
[FONT=&quot]

But the issue is it doesn't correctly pick up the start time & if you time stamp outside the working hours it also doesn't calculate correctly. For example if start time is 07:00 it adds 2 hours from that time when it should add 2 hours from 09:00 as the start time.

thanks,[/FONT]