• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Jonny splitting atoms + added complexity

G

Georgie UK

New Member
Hi.. I'm Georgie.

I've been stuck on this for 2 days and it's driving me mad, I found your Jonny splitting atoms question but it didn't go far enough

I have this scenario.

An IT incident can be logged 24x7 and can be resolved 24x7 but my team of engineers only work Monday to Friday and only 8am to 6pm and not bank holidays

I can easily calculate the entire length of the incident but am struggling to find a simple formula to tell me how many hours my engineers worked on it

I've got as far as 4 conditional logic columns to find me the answer but only if the incident is logged and resolved on the same day and it doesn't know if it's a weekend or holiday so I have failed completely and humbly ask for your support....
 

Attachments

Georgie

Firstly, Welcome to the Chandoo.org Forums

The AND() function is used like using your problem as a sample:
=IF(AND($D5=$H5,$E5<$K5,$I5>$L5),time(10,,),FALSE)

I have applied what i think is the valid formats to your problem below
Please also note that you were entering time as Text "10:00:00" is text and not a time

You can display 10 hrs as either =time(10,,) or simply =10/24
 

Attachments

Hi, Georgie UK!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, give a look at the uploaded file. In the 2nd sheet there's a detailed analysis of the calculations between 2 date/time values, both in total time as in neat time.

Columns:
A: Logged time (don't know why, but it was there)
B: Start time
C: Resolved time
D: Total elapsed time; =C2-B2
E: Engineers start
F: Engineers end
G: Total elapsed time day 1; =SI(ENTERO(C2)=ENTERO(B2);C2-B2;ENTERO(B2)+1-B2) ------> in english: =IF(INT(C2)=INT(B2),C2-B2,INT(B2)+1-B2)
H: Idem entire mid days; =SI(ENTERO(C2)>ENTERO(B2);(ENTERO(C2)-ENTERO(B2)-1);0) -----> in english: =IF(INT(C2)>INT(B2),(INT(C2)-INT(B2)-1),0)
I: Idem day n; =SI(ENTERO(C2)>ENTERO(B2);C2-ENTERO(C2);0) -----> in english: =IF(INT(C2)>INT(B2),C2-INT(C2),0)
J: Sum of G:I
K: Idem J in one formula
L: Neat solving time day 1; =SI(B2-1/24/60<=ENTERO(B2)+F2;ENTERO(B2)+F2-MAX(B2;ENTERO(B2)+E2);0) ------> in english: =IF(B2-1/24/60<=INT(B2)+F2,INT(B2)+F2-MAX(B2,INT(B2)+E2),0)
M: Idem entire mid days; =SI(ENTERO(C2)>ENTERO(B2);(ENTERO(C2)-ENTERO(B2)-1)*(F2-E2);0) -----> in english: =IF(INT(C2)>INT(B2),(INT(C2)-INT(B2)-1)*(F2-E2),0)
N: Idem day n; =SI(Y(ENTERO(C2)>ENTERO(B2);C2+1/24/60>=ENTERO(C2)+E2);MIN(C2;ENTERO(C2)+F2)-ENTERO(C2)-E2;0) -----> in english: =IF(AND(INT(C2)>INT(B2),C2+1/24/60>=INT(C2)+E2),MIN(C2,INT(C2)+F2)-INT(C2)-E2,0)
O: Sum of L:N
P: Idem O in one formula

Just advise if any issue.

Regards!
 

Attachments

Oh NO. sorry this doesn't work.. the last incident in the file... was solved on a Sunday so the engineer time can't be
19 hrs 30 minutes. they dont' work weekends...
 
Hui said:
Georgie

Firstly, Welcome to the Chandoo.org Forums

The AND() function is used like using your problem as a sample:
=IF(AND($D5=$H5,$E5<$K5,$I5>$L5),time(10,,),FALSE)

I have applied what i think is the valid formats to your problem below
Please also note that you were entering time as Text "10:00:00" is text and not a time

You can display 10 hrs as either =time(10,,) or simply =10/24
Click to expand...
 
thanks for that but it still doesn't help me handle either the working days element or the incidents that go over to the next day
 
Georgie UK said:
ENTERO isn't even in my Excel vocabulary :)
Click to expand...
Hi, Georgie UK!
As I use a Spanish Excel version I copy & paste the formulas in my local language (that of ENTERO) followed by the English translation (that of INT that you surely yet knew).
Now about the working days... maybe tomorrow or on Monday I'd be able to give another twist to it.
Regards!
 
My Goodness, that's even more clever.. translating your formulae into Spanish... ! INT yes I know INT..
I can do the workdays if the incident starts and finished on the same day, it's when it goes across days that I get properly confused.. I have been shouting at my PC very annoyed that I paid over £2500 for a set up that doesn't give me visual basic to play with... this is surely not that unusual a requirement...
 
oh you are funny as well as clever! So for a mere £5500 I can have the functionality I had 20 years ago at only 10 times the price! When I started my first graduate job in 1991 with BP and was sent on Excel courses.. VB was part of the basic product... and as a child of the 80s VB was simples! Also the conditional logic builder had loads of layers in it and you could keep adding.. now you have 2 and it's all hard.. Maybe I'm just old.. or less clever than I used to be... but I am depressed that I can't do this..
 
I shouldn't even have to do this.. I am a senior service architect not a reporting analyst.. but none of my young staff got half an education and don't even understand the logic never mind the excel..
Not that I'm complaining.. I think all senior managers should roll their sleeves up. I'm just embarrassed that I can't crack it
 
Hi, Georgie UK!
Georgie UK said:
So for a mere £5500 I can have the functionality I had 20 years ago at only 10 times the price!
Click to expand...
We're in the consume era, you always have to pay more for getting less and harder but it's newer and cool and will solve your life, didn't you know?
Georgie UK said:
When I started my first graduate job in 1991 with BP and was sent on Excel courses.. VB was part of the basic product... and as a child of the 80s VB was simples! Also the conditional logic builder had loads of layers in it and you could keep adding.. now you have 2 and it's all hard..
Click to expand...
In 1991, was I born? Yes, but recently. And yes, VB 1.0 was released in 91, as grandpa told me.
Georgie UK said:
I am a senior service architect not a reporting analyst.. but none of my young staff got half an education and don't even understand the logic never mind the excel..
Click to expand...
About this, use Donald Trump's favorite phrase: you're fired. But keep in mind middle 80's or early 90's Tom Peters book In Search Of Excellence? ... "En una jerarquía todo empleado tiende a ascender hasta alcanzar su nivel de ineficiencia" (Spanish version)... in English something like "In a hierarchy every employee tends to rise to his level of incompetence". And the same applies climbing the hill towards the top direction.
Regards!
 
tom peters... my hero.. I did my masters in quality management.. ah the days... why does this formula not work on my data? I am very stuck

'=NETWORKDAYS(B3,C3)*10-24*(MOD(B3,1)-8/24+18/24-MOD(C3,1))

where B3 is start date and time and C3 is end date and time..
 
Hi, Georgie UK!

Maybe the formula reached its level of incompetence :DD No, I didn't mean (not even thought and even less smile) the user, just the formula...

I don't know how do you count the time in the UK (I should ask my old friend b(ut)ob(ut)hc, but he'd surely tell me that he does the job with an hourglass or a sundial, depending on the weather), but in the rest of the world (exception made of those guys who still use Imperial systems for either weight or distance or capacity, or who invert day and month, ...), if we place this:
B3: 12/12/2013 16:00
C3: 13/12/2013 17:30
D3: =NETWORKDAYS(B3,C3)*10-24*(MOD(B3,1)-8/24+18/24-MOD(C3,1))
you'd get displayed 11.50, that corresponds to:
2*10-24*(2/3-1/3+3/4-35/48)
that's to say:
20-24*17/48
aka:
20-8.50
... et voilà:
11.50
which is correct, 2.00 from 16 to 18, 9.50 from 8 to 17:30.

I only leave you the application of the proper coefficients that you may requiere: .305, .0254, .445, .473, ...

Regards!
 
it's NOT right.. look see here.. Am I being thick or is there something wrong with my Excel.. why doesn't my formula work... have been on this 3 days now. it's ridiculous
 

Attachments

Hi, Georgie UK!

So at last you want me to squeeze my brain (and what if Carlsberg gets spilled?) and analyze your formula... but as lazy as I am you'll have to make your best efforts to achieve that goal. Said best, not that slightly and subtle intent...

While I still decide how to elegantly avoid doing so, maybe you want to give a look again at my 1st comment at this thread, the 3rd one. If I ended with a formula like this:
=SI(B2-1/24/60<=ENTERO(B2)+F2;ENTERO(B2)+F2-MAX(B2;ENTERO(B2)+E2);0)+SI(ENTERO(C2)>ENTERO(B2);(ENTERO(C2)-ENTERO(B2)-1)*(F2-E2);0)+SI(Y(ENTERO(C2)>ENTERO(B2);C2+1/24/60>=ENTERO(C2)+E2);MIN(C2;ENTERO(C2)+F2)-ENTERO(C2)-E2;0) -----> in english: =IF(B2-1/24/60<=INT(B2)+F2,INT(B2)+F2-MAX(B2,INT(B2)+E2),0)+IF(INT(C2)>INT(B2),(INT(C2)-INT(B2)-1)*(F2-E2),0)+IF(AND(INT(C2)>INT(B2),C2+1/24/60>=INT(C2)+E2),MIN(C2,INT(C2)+F2)-INT(C2)-E2,0)
and I didn't write anything superfluous or redundant, i.e., it was my best shot, then I'm not sure that such a simplification will work for every case. Instead of that, I'd better go for adapting it to handle only working days and then compare the final result with your shorter but not always right formula.

Give me a while, please.

Regards!
 
Would it be a bad thing if you had more Carlsberg room in your brain before Christmas? Think of it as a cunning plan!

If you can do this, I'll buy you a beer!
 
Hi, Georgie UK!
Don't tempt me...
It's mid December weekend, 35°C with a higher thermal sensation at your tea time, a swimming pool at a friend's is waiting for me, prior the night barbecue due to a birthday party, so... even I'm the weakest I can regarding Carlsberg -not just "beer"-. I think that you'll have to raise the stake muuuch higher. :cool:
Don't tempt me... unless you do it seriously! ;)
Regards!
 
Hi Georgie,

B3: 12/12/2013 16:00
C3: 13/12/2013 17:30
D3: =NETWORKDAYS(B3,C3)*10-24*(MOD(B3,1)-8/24+18/24-MOD(C3,1))
you'd get displayed 11.50, that corresponds to:
2*10-24*(2/3-1/3+3/4-35/48)
that's to say:
20-24*17/48
aka:
20-8.50
... et voilà:
11.50
Click to expand...

The above mentioned formula does work for me as well, I think there must have been some formatting that is messing the things...
 
=NETWORKDAYS(B28,C28)*10-IF(NETWORKDAYS(B28,B28)=1,IF(TIME(HOUR(B28),MINUTE(B28),SECOND(B28))*24>18,10,IF(TIME(HOUR(B28),MINUTE(B28),SECOND(B28))*24<8,0,(TIME(HOUR(B28),MINUTE(B28),SECOND(B28))*24-8))),0)-IF(NETWORKDAYS(C28,C28)=1,IF(TIME(HOUR(C28),MINUTE(C28),SECOND(C28))*24>18,0,IF(TIME(HOUR(C28),MINUTE(C28),SECOND(C28))*24<8,10,(18-TIME(HOUR(C28),MINUTE(C28),SECOND(C28))*24))),0)

I think this formula would calculate the answer you want for a start time in B28 and resolved time in C28
 
You must log in or register to reply here.
Back
Top