Calculate Hours difference

[Chetan]

Hello Everyone,

I have Incident Open Date & Time and Incident Close Date & Time.
I need to calculate the hours difference between these 02 dates & time.

It has some condition as below:
The hours difference calculated is only between morning 08:30 to evening 06:30. Difference counter stops as soon as it reaches to evening 06:30 and again it is resumes to next day morning at 08:30.

For example: if the incident Open date and time is 01-July-14 09:30:00 and incident Close date & time is 02-July-14 10:30:00. then the actual (expected output) is 12:00:00 hrs.

Please refer to attached file.

Thanks,
Chetan

 

[Mike H..]

Hello Everyone,

I have Incident Open Date & Time and Incident Close Date & Time.
I need to calculate the hours difference between these 02 dates & time.

It has some condition as below:
The hours difference calculated is only between morning 08:30 to evening 06:30. Difference counter stops as soon as it reaches to evening 06:30 and again it is resumes to next day morning at 08:30.

For example: if the incident Open date and time is 01-July-14 09:30:00 and incident Close date & time is 02-July-14 10:30:00. then the actual (expected output) is 12:00:00 hrs.

Please refer to attached file.

Thanks,
Chetan

Hi,

See the attached workbook. Changes in that workbook are:-

1. H1= Time of working day start.
2. I1 = Time of end of working day
3. O1:O10 = Named range for holidays
4. Col F = Hours difference as decimal
5. Col G = Hours difference in HH:MM

The formula will only work in E2010 and later

=((NETWORKDAYS.INTL(B2+C2,D2+E2,11,Holidays)-1)*($I$1-$H$1)+IF(NETWORKDAYS.INTL(D2+E2,D2+E2,11,Holidays),MEDIAN(MOD(D2+E2,1),$I$1,$H$1),$I$1)-MEDIAN(NETWORKDAYS.INTL(B2+C2,B2+C2,11,Holidays)*MOD(B2+C2,1),$I$1,$H$1))*24

 

[Chetan]

What a

Hi,

See the attached workbook. Changes in that workbook are:-

1. H1= Time of working day start.
2. I1 = Time of end of working day
3. O1:O10 = Named range for holidays
4. Col F = Hours difference as decimal
5. Col G = Hours difference in HH:MM

The formula will only work in E2010 and later

=((NETWORKDAYS.INTL(B2+C2,D2+E2,11,Holidays)-1)*($I$1-$H$1)+IF(NETWORKDAYS.INTL(D2+E2,D2+E2,11,Holidays),MEDIAN(MOD(D2+E2,1),$I$1,$H$1),$I$1)-MEDIAN(NETWORKDAYS.INTL(B2+C2,B2+C2,11,Holidays)*MOD(B2+C2,1),$I$1,$H$1))*24

Perfect !!!

Kudos to you. Thanks a tone for your help.

Cheers

 

[Chetan]

Mike hi,

Can i please add some more conditions to my querry ?
Can the TAT calculation be the State specific.? in your solution you have a general holidays.

I have attached the revised file (which is my actual working file).



Thanks and Sorry i was not clear in my first question.

Cheers,
Chetan

 

[Mike H..]

Mike hi,

Can i please add some more conditions to my querry ?
Can the TAT calculation be the State specific.? in your solution you have a general holidays.

I have attached the revised file (which is my actual working file).



Thanks and Sorry i was not clear in my first question.

Cheers,
Chetan

Hi,

Yes you can do that but it's a bit of work for you to do. By my count you have 23 different states so if none of the states have the same holidays then you need to create 23 named ranges that hold each states holidays and in the formula refer to that state like this example for West Bengal

=((NETWORKDAYS.INTL(B3+C3,D3+E3,11,WestBengal)-1)*($I$1-$H$1)+IF(NETWORKDAYS.INTL(D3+E3,D3+E3,11,WestBengal),MEDIAN(MOD(D3+E3,1),$I$1,$H$1),$I$1)-MEDIAN(NETWORKDAYS.INTL(B3+C3,B3+C3,11,WestBengal)*MOD(B3+C3,1),$I$1,$H$1))*24

Note that a named range can't have a space in the name.

If any of the states do have common holidays then you can get away with fewer named ranges but you will have to devise a naming convention that you will recognise as representing 2 or more states.

 

[Chetan]

Hi,

Yes you can do that but it's a bit of work for you to do. By my count you have 23 different states so if none of the states have the same holidays then you need to create 23 named ranges that hold each states holidays and in the formula refer to that state like this example for West Bengal

=((NETWORKDAYS.INTL(B3+C3,D3+E3,11,WestBengal)-1)*($I$1-$H$1)+IF(NETWORKDAYS.INTL(D3+E3,D3+E3,11,WestBengal),MEDIAN(MOD(D3+E3,1),$I$1,$H$1),$I$1)-MEDIAN(NETWORKDAYS.INTL(B3+C3,B3+C3,11,WestBengal)*MOD(B3+C3,1),$I$1,$H$1))*24

Note that a named range can't have a space in the name.

If any of the states do have common holidays then you can get away with fewer named ranges but you will have to devise a naming convention that you will recognise as representing 2 or more states.

Mike hi,

Thanks for your reply. will sure try to get created name ranges.

just last question, why you have used hours output format as [hh]:mm, why not hh:mm.

Thanks for all your help.

Cheers,
Chetan

 

[Somendra Misra]

Hi Chetan,

If you just create named range as below and named it as Holiday, and than use that named range in @Mike H.. formula that will account for different states as per row.

=OFFSET(Settings!$F$4,MATCH(Table2[@State],Table1[State],0)-1,,COUNTIF(Table1[State],Table2[@State]))

Regards,

 

[Mike H..]

Mike hi,

Thanks for your reply. will sure try to get created name ranges.

just last question, why you have used hours output format as [hh]:mm, why not hh:mm.

Thanks for all your help.

Cheers,
Chetan

Hi,

If you use a format of HH:MM then after 24 hours it rolls over to zero. Using the format [HH]:MM stops that rollover.