View original (French)

HO-HNO separation in Excel

Solved
micbru14 Posted messages 26 Status Member -  
micbru14 Posted messages 26 Status Member -
Hello,
I have a table of alarms that summarizes equipment downtime by recording the cutoff time and the restoration time.
I currently have a table where I calculate the difference in hours between two cells in the format "dd/mm/yyyy hh:mm".
My result cell is in the format [hh]:mm:ss, so I can have results like "112:45:32" if the equipment outage lasts several days.
I want to improve this table and obtain two results for this difference:
- in one cell, the duration of non-working hours downtime (HNO), namely, during the week from 6:00 PM to 8:00 AM the next day and on weekends from Friday 6:00 PM to Monday 8:00 AM
- in the adjacent cell, the duration of working hours downtime (HO), namely from Monday to Friday from 8:00 AM to 6:00 PM

Thank you for your help

See you soon.

13 answers

Answer 1 / 13
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 
Hello,
Is it possible to have an example of your table?
--

Regards.
Jean-Pierre
0
micbru14 Posted messages 26 Status Member
  
Here is an excerpt from my table: the formula entered in column D is: =IF(D4="Contact Established",B4-B3,"") the format of the cells in column A is: dd/mm/yyyy hh:mm the format of the cells in column D is: [h]:mm A B C D 1 Date - Time Eqpt Event Outage 2 2/2/10 8:00 toto contact lost 3 2/2/10 8:35 toto contact established 0:35 4 3/2/10 13:10 titi contact lost 5 3/2/10 19:15 titi contact established 6:05 6 5/2/10 14:00 tata contact lost 7 8/2/10 9:10 tata contact established 67:10 What I would like to obtain is presented as the example below: - Example 1, it is a weekday (Tuesday) and the outage is between 8am and 6pm. - Example 2, it is a weekday (Wednesday) and the outage is between 1:10pm and 7:15pm, which is 4h50 in regular hours from 1:10pm to 6:00pm and 1h15 in outside hours after 6:00pm - Example 3, the outage is from Friday before 6:00pm until Monday after 8:00am, which is 4h00 in regular hours before 6:00pm on Friday + 1h10 in regular hours after 8:00am on Monday and 62h00 in outside hours from Friday 6:00pm to Monday 8:00am - Example 4, the outage occurs between Saturday and Sunday which is only outside hours A B C D E 1 Date - Time Eqpt Event Regular Hours Outside Hours 2 2/2/10 8:00 toto contact lost 3 2/2/10 8:35 toto contact established 0:35 0:00 4 3/2/10 13:10 titi contact lost 5 3/2/10 19:15 titi contact established 4:50 1:15 6 5/2/10 14:00 tata contact lost 7 8/2/10 9:10 tata contact established 5:10 62:00 I tried changing the date format in column A to a predefined type: *Wednesday, March 14, 2001 just to ensure that Excel properly recognizes the weekday and keeps the time even if it only displays in the reading pane. Thank you for your help
0
micbru14 Posted messages 26 Status Member
 
I'm sorry, but I just realized that the formatting of my response is terrible and therefore incomprehensible. I will try to fix it by writing the contents of the cells one after the other.
You will need to interpret because I could not find how to integrate Excel tables into my messages.
1st table:

The formula entered in column D is: =IF(D4="Contact ok",B4-B3,"")
The format of the cells in column A is: dd/mm/yyyy hh:mm
The format of the cells in column D is: [h]:mm

A1:"Date-time", B1:"Equipment", C1:"contact", D1:"Downtime duration"
A2:"02/02/10 08:00", B2:"toto", C2:"contact lost", D2:""
A3:"02/02/10 08:35", B3:"toto", C3:"contact ok", D3:"0:35"
A4:"03/02/10 13:10", B4:"tata", C4:"contact lost", D4:""
A5:"03/02/10 19:15", B5:"tata", C5:"contact ok", D5:"6:05"
A6:"05/02/10 14:00", B6:"tutu", C6:"contact lost", D6:""
A7:"08/02/10 09:10", B7:"tutu", C7:"contact ok", D7:"67:10"
A8:"06/02/10 14:00", B8:"tutu", C8:"contact lost", D8:""
A9:"07/02/10 09:10", B9:"tutu", C9:"contact ok", D9:"67:10"

What I would like to obtain is presented as the example below:
- Example 1, it is a weekday (Tuesday) and the downtime is between 8 AM and 6 PM.
- Example 2, it is a weekday (Wednesday) and the downtime is between 1:10 PM and 7:15 PM
that is 4h50 in HO from 1:10 PM to 6:00 PM and 1h15 in HNO after 6:00 PM
- Example 3, the downtime is from Friday before 6:00 PM until Monday after 8:00 AM
that is 4:00 HO before 6 PM on Friday + 1h10 HO after 8 AM on Monday and
and 62h00 HNO from Friday 6 PM to Monday 8 AM
- Example 4, the downtime occurs between Saturday and Sunday, which means only HNO

A1:"Date-time", B1:"Equipment", C1:"contact", D1:"HO", E1:"HNO"
A2:"02/02/10 08:00", B2:"toto", C2:"contact lost", D2:"", E2:""
A3:"02/02/10 08:35", B3:"toto", C3:"contact ok", D3:"0:35", E3:"0:00"
A4:"03/02/10 13:10", B4:"tata", C4:"contact lost", D4:"", E4:""
A5:"03/02/10 19:15", B5:"tata", C5:"contact ok", D5:"4:50", E5:"1:15"
A6:"05/02/10 14:00", B6:"tutu", C6:"contact lost", D6:"", E6:""
A7:"08/02/10 09:10", B7:"tutu", C7:"contact ok", D7:"5:10", E7:"62:00"
A8:"06/02/10 14:00", B8:"tutu", C8:"contact lost", D8:"", E8:""
A9:"07/02/10 09:10", B9:"tutu", C9:"contact ok", D9:"0:00", E9:"19:10"

I tried changing the date format of column A to a predefined type: "*Wednesday 14 March 2001" just to make sure Excel has the notion of the day of the week and keeps the time even if it only appears in the reading pane.

I hope this message is a bit clearer and Thank you for your help
0
Answer 2 / 13
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 
Hello,
Thank you, I will look everything over and provide a response by tomorrow.
Note:
It is also possible to upload the file (table) to https://www.cjoint.com/ and post the link, it's simpler.
--

Regards.
Jean-Pierre
0
Answer 3 / 13
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 
Hello,
Here is my proposal, hoping it is in the right direction: https://www.cjoint.com/?clxBk2ZP3C
--

Best regards.
Jean-Pierre
0
Answer 4 / 13
micbru14 Posted messages 26 Status Member
 
Hello,
"Hats off" your solution works wonderfully and if I may, I would like to take advantage of your knowledge a little more to find out what modifications should be made to the macro to consider Saturday as a working day as well.
Anyway, thank you very much.

Best regards.

Micbru14
0
Answer 5 / 13
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 
Hello,
Modify this line:
If Weekday(datdeb + c) = 1 Or Weekday(datdeb + c) = 7 Then ' Sunday / Saturday (non-working)
If Weekday(datdeb + c) = 1 Then ' Sunday (non-working)

--

Best regards.
Jean-Pierre
0
Nono
 
Awesome!
0
Answer 6 / 13
micbru14 Posted messages 26 Status Member
 
Hello,
sorry for taking so long to thank you (holidays!).
It's perfect
Thanks a lot
0
Answer 7 / 13
micbru14 Posted messages 26 Status Member
 
Hello,
I am reaching out again because I encountered a problem with the macro.
Indeed, when the interruptions occur during the week and outside of operating hours, it indicates errors. A small message alerts me to negative hours (?????).
This phenomenon does not occur when the interruptions are on the weekend, even between 6:00 PM and 8:00 AM.
I have included this example in the attached file. https://www.cjoint.com/?dbnGZ8JlTf
Can you please help me?

Best regards
0
Answer 8 / 13
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 
Hello,
Thank you for taking this corrected version: https://www.cjoint.com/?dbwXLvKUwJ
--

Best regards.
Jean-Pierre
0
Answer 9 / 13
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 
Hello micbru14,
I sent you the wrong file, sorry, thank you for checking the private message
--

Best regards.
Jean-Pierre
0
Answer 10 / 13
micbru14 Posted messages 26 Status Member
 
Hello,
I still have a problem with the macro (see: http://cjoint.com/?ddljaFbYjE).
The issue of negative hours is now in column HNO.
My first attempt was okay, but the cut-off occurred on the same day.
It seems that it doesn't work when the cut-off spans two dates.

Thank you for your help.

Best regards
0
Answer 11 / 13
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 
Hello,
Is this the version I sent you via PM?
--

Regards.
Jean-Pierre
0
Answer 12 / 13
Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
 
Hello,
Replace the existing procedure with the following: 
Function NbHeuJourOuv(dd, df, hd, hf) Dim datdeb, datfin, hdeb, hfin, nbj Dim houvd, houvf, hnhjo Dim c As Byte, test, fin houvd = CDate(hd) houvf = CDate(hf) datdeb = dd datfin = df hdeb = datdeb - 1 * Int(datdeb / 1) hfin = datfin - 1 * Int(datfin / 1) nbj = DateDiff("d", datdeb, datfin) For c = 0 To nbj test = datdeb + c If Weekday(datdeb + c) = 1 Or Weekday(datdeb + c) = 7 Then ' MsgBox "Saturday / Sunday" Else ' MsgBox "workable" If nbj = 0 Then If houvd < hdeb And hfin <= houvf Then hnhjo = hfin - hdeb ElseIf hdeb < houvd And (hfin > houvd And hfin <= houvf) Then hnhjo = (hfin - houvd) ElseIf (hdeb >= houvd And hdeb < houvf) And hfin > houvf Then hnhjo = houvf - hdeb ElseIf hdeb < houvd And hfin > houvf Then hnhjo = houvf - houvd End If Else If test = datdeb Then If houvd < hdeb And hdeb < houvf Then hnhjo = hnhjo + houvf - hdeb ElseIf hdeb < houvd Then hnhjo = hnhjo + (houvf - houvd) End If ElseIf Format(test, "dd mm yyyy") = Format(datfin, "dd mm yyyy") Then If houvd < hfin And hfin < houvf Then hnhjo = hnhjo + hfin - houvd ElseIf hfin > houvf Then hnhjo = hnhjo + (houvf - houvd) End If Else hnhjo = hnhjo + (houvf - houvd) End If End If End If Next c NbHeuJourOuv = hnhjo End Function

--

Regards.
Jean-Pierre
0
Answer 13 / 13
micbru14 Posted messages 26 Status Member
 
Hello,
everything is "great" now;
Thanks again for your help.

Best regards.

Michel
0