Formula to get delay time

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Dossfm0q

Active Member
Joined
Mar 9, 2009
Messages
402
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"))
 

Dossfm0q

Active Member
Joined
Mar 9, 2009
Messages
402
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"))
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,169
Members
410,775
Latest member
alal1030
Top