Calculating difference between dates and times

K

Kaurr

New Member
Joined
Nov 9, 2014
Messages
2
Hi guys, I am trying to calculate downtime between two dates.

Taking the first as example: Outage started on 1st and ended on 19th. Now Between 1st and 19th, I only want to calculate outage from 10:00 to 20:00 (support hours).

Taking the second row as example - clearly on sunday (5th Oct), there is the outage of 9 mins however it calculates 10 hours (total support hours)..??

[TABLE="width: 568"]
<TBODY>[TR]
[TD="class: xl75, width: 108, bgcolor: #ffc000"]Started
[/TD]
[TD="class: xl75, width: 108, bgcolor: #ffc000"]Ended
[/TD]
[TD="class: xl76, width: 61, bgcolor: #ffc000"]Total Duration
[/TD]
[TD="class: xl69, width: 74, bgcolor: #4bacc6"]Service Start Time (Mon-Sun)
[/TD]
[TD="class: xl69, width: 73, bgcolor: #4bacc6"]Service end time (Mon-Sun)
[/TD]
[TD="class: xl69, width: 61, bgcolor: #4bacc6"]Service Hrs (Sun)
[/TD]
[TD="class: xl69, width: 84, bgcolor: #4bacc6"]Service Support Type
[/TD]
[TD="class: xl69, width: 94, bgcolor: #4bacc6"]Outage hrs
[/TD]
[TD="class: xl69, width: 92, bgcolor: #4bacc6"]Outage hrs (Sunday)
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]01-10-2014 10:14
[/TD]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]19-10-2014 21:28
[/TD]
[TD="class: xl74, bgcolor: #fde9d9"]26593
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]20:00
[/TD]
[TD="class: xl77, width: 61, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10x7 Support
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]159:45:01
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]30:00:00
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 10:13
[/TD]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 10:23
[/TD]
[TD="class: xl74, bgcolor: #fde9d9"]9
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]20:00
[/TD]
[TD="class: xl77, width: 61, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10x7 Support
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]0:00:00
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]10:00:00
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 17:56
[/TD]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 23:43
[/TD]
[TD="class: xl74, bgcolor: #fde9d9"]346
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]20:00
[/TD]
[TD="class: xl77, width: 61, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10x7 Support
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]0:00:00
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]10:00:00
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 18:00
[/TD]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 18:20
[/TD]
[TD="class: xl74, bgcolor: #fde9d9"]19
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]20:00
[/TD]
[TD="class: xl77, width: 61, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10x7 Support
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]0:00:00
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]10:00:00
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 18:17
[/TD]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 18:35
[/TD]
[TD="class: xl74, bgcolor: #fde9d9"]18
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]20:00
[/TD]
[TD="class: xl77, width: 61, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10x7 Support
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]0:00:00
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]10:00:00
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 18:37
[/TD]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 19:00
[/TD]
[TD="class: xl74, bgcolor: #fde9d9"]22
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]20:00
[/TD]
[TD="class: xl77, width: 61, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10x7 Support
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]0:00:00
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]10:00:00
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 20:04
[/TD]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 20:46
[/TD]
[TD="class: xl74, bgcolor: #fde9d9"]41
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]20:00
[/TD]
[TD="class: xl77, width: 61, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10x7 Support
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]0:00:00
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]10:00:00
[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 20:39
[/TD]
[TD="class: xl70, bgcolor: #fde9d9, align: right"]05-10-2014 21:04
[/TD]
[TD="class: xl74, bgcolor: #fde9d9"]25
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]20:00
[/TD]
[TD="class: xl77, width: 61, bgcolor: #daeef3"]10:00
[/TD]
[TD="class: xl72, bgcolor: #daeef3"]10x7 Support
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]0:00:00
[/TD]
[TD="class: xl73, bgcolor: #daeef3"]10:00:00
[/TD]
[/TR]
</TBODY>[/TABLE]



Using the following formula i am able to calculate the outage from Monday to saturday (Outage hrs)
=IF(C2="",0,(NETWORKDAYS.INTL(B2,C2,11)-1)*(F2-E2)+IF(NETWORKDAYS.INTL(C2,C2,11),MEDIAN(MOD(C2,1),E2,F2),F2)-MEDIAN(NETWORKDAYS.INTL(B2,B2,11)*MOD(B2,1),E2,F2))

but unable to calculate the downtime of sundays (outage hours (sunday)), for which I am using the following formula:

=IFERROR(G2*SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(B2)&":"&INT(C2))))=1)),0)

B2 - Start date
C2 - End Date
D2 - Total Duration
E2 - Service start time
F2 - Service end time
G2 - serice hours sunday
H2 - Service support type
J2 - outage hours
K2 - Outage hours (sun)


Thanks for your help in advance!!
 
Sort by date Sort by votes
Try this:


Excel 2010
BCDEFGHIJ
1StartedEndedTotal DurationService Start Time (Mon-Sun)Service end time (Mon-Sun)Service Hrs (Sun)Service Support TypeOutage hrsOutage hrs (Sunday)
2Oct. 1, 2014 10:14Oct. 19, 2014 21:282659410:0020:0010:0010x7 Support159:46:0030:00:00
3Oct. 5, 2014 10:13Oct. 5, 2014 10:231010:0020:0010:0010x7 Support0:00:000:10:00
4Oct. 5, 2014 17:56Oct. 5, 2014 23:4334710:0020:0010:0010x7 Support0:00:002:04:00
5Oct. 5, 2014 18:00Oct. 5, 2014 18:202010:0020:0010:0010x7 Support0:00:000:20:00
6Oct. 5, 2014 18:17Oct. 5, 2014 18:351810:0020:0010:0010x7 Support0:00:000:18:00
7Oct. 5, 2014 18:37Oct. 5, 2014 19:002310:0020:0010:0010x7 Support0:00:000:23:00
8Oct. 5, 2014 20:04Oct. 5, 2014 20:464210:0020:0010:0010x7 Support0:00:000:00:00
9Oct. 5, 2014 20:39Oct. 5, 2014 21:042510:0020:0010:0010x7 Support0:00:000:00:00
Sheet3
Cell Formulas
RangeFormula
D2=(C2-B2)*1440
I2=IF(C2="",0,(NETWORKDAYS.INTL(B2,C2,11)-1)*(F2-E2)+IF(NETWORKDAYS.INTL(C2,C2,11),MEDIAN(MOD(C2,1),E2,F2),F2)-MEDIAN(NETWORKDAYS.INTL(B2,B2,11)*MOD(B2,1),E2,F2))
J2=IF(C2="",0,(NETWORKDAYS.INTL(B2,C2,"1111110")-1)*(F2-E2)+IF(NETWORKDAYS.INTL(C2,C2,"1111110"),MEDIAN(MOD(C2,1),E2,F2),F2)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"1111110")*MOD(B2,1),E2,F2))
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel formula to allow display number of hours in particular periods of time
Replies
1
Views
193
myall_blues
myall_blues
C
  • Solved
Calculate hour total for each week
2
Replies
16
Views
292
CaptainGravyBum
C
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
549
dshtesy
D
A
  • Question Question
Counting hours spent ( within certain shift hours) between two dates.
Replies
1
Views
492
eiloken
E
M
  • Question Question
Complexed Time Difference Formula
2
Replies
17
Views
2K
mahmed1
M

Share this page

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