Time Calculation if day overlap from limit

[bari2jitu]

Hi All,

I am having breakdown sheet for equipment. in that I need to calculate the breakdown time by weekly basis.

Now, I am facing the problem is due to 1st week last date and 2nd week first date overlapping.

In the sheet 8th jan-2018 is last day of first week as per our requirement, and 9th Jan-2018 is first day of 2nd week. But in calculation i required only up to calculation of 8th jan2018 23:59:59 without multi entry in the summury.

please support.

 

[vletm]

bari2jitu
test for cells [J5]
=IF(AND(C5>0,D5>0,D5>C5,C5<=K$3,D5>=J$3),-IF(C5<J$3,J$3-C5,0)+(D5-C5)+IF(D5>K$3,K$3-D5,0),"")
Are You sure about result in [F6]?

 

[bari2jitu]

thanks for support vletm,

Find the attached sheet, i need equipment wise so the formula you provide it will work for only one detail.

please support.

 

[vletm]

bari2jitu
How about that [F6] result in Your original version?

If You change You case then of course any formula won't necessary work with any case.
There are 'some links' in that file!
How would You image to do formulas with those Your external files?
Would this version be the 'final version' of Your case or how?

 

[bari2jitu]

thanks vletm,

Your formula is working fine with one cell, And i am happy with the same.
Some formula i tried in that sheet which not give satisfactory.

attached sheet is final sheet for me.

please help to sort out issue

thanks

 

[vletm]

Hmm ... You skipped many lines from my Reply #4.

It would be challenge to help You if You won't help to give answers!

 

[bari2jitu]

I want calculation in this table as per equipment wise

 

[bari2jitu]

bari2jitu
How about that [F6] result in Your original version?

If You change You case then of course any formula won't necessary work with any case.
There are 'some links' in that file!
How would You image to do formulas with those Your external files?
Would this version be the 'final version' of Your case or how?

sorry for inconvenience
please ignore the links.

 

[vletm]

I didn't get idea of Your cell [F6] result in original file!
I won't ask again!

Press [ I WANT ]-button.

 

[bari2jitu]

I didn't get idea of Your cell [F6] result in original file!
I won't ask again!

Press [ I WANT ]-button.

Heartily Thanks vletm for great support.
The VB code very supportive for me. but cant calculate (- minus) holidays and Sundays from count. And my daily working hours are only 8 hours (06:30AM to 03:30PM). i want to calculate only between work hours.

extremely thanks if get this support.

 

[vletm]

I - #4 Reply: Would this version be the 'final version' of Your case or how?
You - #5 Reply: attached sheet is final sheet for me.
... and how about lunch hours ... and other breaks ... ?
Where were marked Your ... holidays or not Sundays in that file?
Is it possible to try to write ... as many ...'small details' at once?
... and still ... You want ...
You would like to get something - but what?

 

[bari2jitu]

#4 Reply: Would this version be the 'final version' of Your case or how?
Me: I wanted to try myself but i am not that much expert in VBA so i given only examples, now i am attached Final verson of my case will not be differ.

... and how about lunch hours ... and other breaks ... ?
Me: Yes, The breaks will be there for 30 min (11:00 to 11:30)
Where were marked Your ... holidays or not Sundays in that file?
Is it possible to try to write ... as many ...'small details' at once?
Me: Yes, we can mark all details in the attached sheet, It is possible to write as many small details at once in Avl sheet.
You would like to get something - but what?
Me: Please find the Final attached file. Summery sheet details filled by the operator and Avl sheet will be generate automatically.

Whether can we do these both separate files? Monthly basis.

Note1: some sheet are hidden.
Note2: Total month count will be as per particular month only.(D224)
Note3: Graph and chart is for 2018

any addition and deletion accept in Avl sheet.

As simple as i require.

I glade to thanks yo infinity time :D

 

[vletm]

Maybe You didn't get an idea?
If Your so called 'Final version' always change then You just waist time!
If You always change Your so called idea then You just waist time!
Should I write what would I want ... no, no ... what would I would like to get?

 

[bari2jitu]

Sorry for inconvenience cause.
But reply #12 is the final one.

I know that due communication gap from my side the things not going smoothly. But thanks for the support.

i got some clues from your support still i am in trouble due to my limited knowledge..

Have a nice year ahead dear. happy New Year.

:D

 

[bari2jitu]

HI,

I had done my work as guided by you, Thanks a lot.

but the problem i facing with the same date entry in start date and End date. If both are same then macro run continuously in bold loop mentioned below.

Please find the attached file as well .

Do
chk_c1 = chk_c1 + 1
If chk_c1 < d_max And chk_c1 + 1 < chk_g Then
chk_c1 = chk_c1 + 1
x1 = 3
Do
x1 = x1 + 1
Loop Until .Cells(3, x1 + 1) > chk_c1 Or .Cells(3, x1 + 1) = Empty
Availability
With ActiveSheet
.Cells(yy, x1) = .Cells(yy, x1) + 1
.Cells(yy, x1).NumberFormat = "[h]:mm"
End With
End If
Loop Until chk_c1 < d_max And chk_c1 < chk_g
T3 = chk_g - chk_c1
If T3 > 0 Then
x1 = 3
Do
x1 = x1 + 1
Loop Until .Cells(3, x1 + 1) > chk_c1 Or .Cells(3, x1 + 1) = Empty
Availability
With ActiveSheet
.Cells(yy, x1) = .Cells(yy, x1) + T3
.Cells(yy, x1).NumberFormat = "[h]:mm"
End With
End If

 

[vletm]

bari2jitu
1) Use code tags if You really have to try to paste code here!
2) You have done 'minor modifications' to sheets layouts ...
BUT You haven't modified code as needed!
3) I copied 'Your' Summary!E3:G13 to original file ... and it seems to work!
>> I quickly check few details
are You 100% sure that Your changed 'ActiveSheet' are correct?
It would be challenge to handle TWO ACTIVESHEET
while copying data from 1st 'ActiveSheet' to 2nd 'ActiveSheet'!
It's possible but ... I wouldn't do so!
The original version has only ONE sheet ... Yours TWO sheets >>
do limits has correct values to take care loops?

>> If You modify layout and code ... do it careful and step-by-step <<

 

[bari2jitu]

1) Use code tags if You really have to try to paste code here!
Thanks, i will take care for paste VBA code in the forum next time.

2) You have done 'minor modifications' to sheets layouts ...
BUT You haven't modified code as needed!

As per layout i had done minor changes, Also checked , work fine.

3) I copied 'Your' Summary!E3:G13 to original file ... and it seems to work!

Yes, it is working with my system also. Only issue is with Same date in F:F and G:G. Then it will go in infinite loop.

are You 100% sure that Your changed 'ActiveSheet' are correct?
Yes, it is correct.

The original version has only ONE sheet ... Yours TWO sheets >>
this is the original one.

do limits has correct values to take care loops?
Any Other Option are suggest.?

>> If You modify layout and code ... do it careful and step-by-step <<
That only i learnt from you dear...Thanks for that.

 

[vletm]

3) I copied 'Your' Summary!E3:G13 to original file ... and it seems to work!
Yes, it is working with my system also. Only issue is with Same date in F:F and G:G. Then it will go in infinite loop.
I don't have sample values and
*) I don't have code for newer version which would check!

are You 100% sure that Your changed 'ActiveSheet' are correct?
Yes, it is correct.
I'm not so sure that is correct! (*

1) The original version has only ONE sheet ... Yours TWO sheets >>
this is the original one. >> this is the original one!

do limits has correct values to take care loops?
Any Other Option are suggest.?
... means ... You didn't verify and I won't do it with that code!

>> If You modify layout and code ... do it careful and step-by-step <<
That only i learnt from you dear...Thanks for that.
As written ... do it careful and step-by-step!

... and this You the latest version isn't version,
which You would like to use!