Formula to get delay time

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
530
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi All,

Thanks

we have the following sheet

One
Two
Three
Plan DateActual DateTime DelayStatus
10/28/2020 18:00:0010/27/2020 10:10:29OK
10/28/2020 18:00:0010/27/2020 10:10:29OK
10/28/2020 18:00:0010/29/2020 10:10:29OK
10/16/2020 18:00:0010/15/2020 13:55:28OK
10/16/2020 18:00:0010/15/2020 17:13:35OK
10/20/2020 18:00:0010/19/2020 16:09:26OK
10/20/2020 18:00:0010/21/2020 17:17:39OK


now we want the delay time in column Sheet1!C5:C11 but want to exclude the holyday, sunday and time delay will be count as per office hours

Sunday, Holiday and Office Hours are
SundayHolidayOffice Timing
10-06-202008-10-202011:00
13-10-202022-10-202018:00
20-10-2020
27-10-2020


help pls
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Book1
ABCD
1One
2Two
3Three
4Plan DateActual DateTime DelayStatus
510/28/20 18:00:0010/27/20 10:10:291 Day 07:49:31OK
610/28/20 18:00:0010/27/20 10:10:291 Day 07:49:31OK
710/28/20 18:00:0010/29/20 10:10:29-16:10:29OK
810/16/20 18:00:0010/15/20 13:55:281 Day 04:04:32OK
910/16/20 18:00:0010/15/20 17:13:351 Day 00:46:25OK
1010/20/20 18:00:0010/19/20 16:09:261 Day 01:50:34OK
1110/20/20 18:00:0010/21/20 17:17:39-23:17:39OK
12
Sheet1
Cell Formulas
RangeFormula
C5:C11C5=CONCATENATE(IF(A5-B5<0,"-",""),IF(TRUNC(A5-B5)<>0,TRUNC(A5-B5),""),IF(TRUNC(A5-B5)>1, " Days ",IF(TRUNC(A5-B5)<1, ""," Day ")),TEXT(IF(A5-B5<0,-1*(A5-B5)-TRUNC(A5-B5),(A5-B5)-TRUNC(A5-B5)),"hh:mm:ss"))
 
Upvote 0
Hi

Book1
ABCD
1One
2Two
3Three
4Plan DateActual DateTime DelayStatus
510/28/20 18:00:0010/27/20 10:10:291 Day 07:49:31OK
610/28/20 18:00:0010/27/20 10:10:291 Day 07:49:31OK
710/28/20 18:00:0010/29/20 10:10:29-16:10:29OK
810/16/20 18:00:0010/15/20 13:55:281 Day 04:04:32OK
910/16/20 18:00:0010/15/20 17:13:351 Day 00:46:25OK
1010/20/20 18:00:0010/19/20 16:09:261 Day 01:50:34OK
1110/20/20 18:00:0010/21/20 17:17:39-23:17:39OK
12
Sheet1
Cell Formulas
RangeFormula
C5:C11C5=CONCATENATE(IF(A5-B5<0,"-",""),IF(TRUNC(A5-B5)<>0,TRUNC(A5-B5),""),IF(TRUNC(A5-B5)>1, " Days ",IF(TRUNC(A5-B5)<1, ""," Day ")),TEXT(IF(A5-B5<0,-1*(A5-B5)-TRUNC(A5-B5),(A5-B5)-TRUNC(A5-B5)),"hh:mm:ss"))
 
Upvote 0
Thanks Dossfm0q for the solution

still in my one query one point is pending

Sheet1!A7 - task date is 10/28/2020 18:00:00 and task done at 10/29/2020 10:10:29
Office timing is 11:00 am to 6:00 pm
work not done on 10/28/2020 but next day before office time its done, so there is no delay, how can we do that
we are calculating delay according to office hours

also if there is Sunday or holiday we will not count it because in that day office is close so we cant count it as delay
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top