Date and Time diffrence 2

[TenBellie]

Hi,

Can anyone help me work out a formula in excel to calculate time diffrence when part of the window stops..

EG

Flt reported 07/03/2014 12:55 flt closed 08/03/2014 13:30

The clock stops between 01:00 and 8:00 so this time is not part of the duration to fix the flt...

I have many rows of data....

Thanks in advance...

 

[SkipVought]

Hi,

Don't understand what you mean, but to calculate duration, just take the difference between the end and start Date/Time values.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[bluedragon2]

If I understand your question correctly, you want the total time between the two dates and exclude any time that falls between 0100-0800.

If that is correct, you can do difference between the times and exclude 7 hours per day. The caveat is determining if the begin or end times fall within, preclude, or fall after the exclusion times. Therefore, you would need to include the IF/THEN's within the formula.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[kjv1611]

TenBellie,

I think you're going to have to give a little more background/details on your question if you want to ensure you get the correct answer.

For instance, it also sounds like you may have multiple rows for one record/entity. In that case, you could simply use EndTime - BeginTime formula on each row, and then sum up, or pivot, the results by entity/record to get the total.

If this latest guess is correct, your records look something like this:

RecordID Start Finish
111 8:00 10:00
111 11:00 12:00
111 12:30 17:00
222 7:45 11:00
222 11:45 14:30

Anyway - if you're not getting what you need, be sure to post back with more details. Otherwise, be sure to let us know what DID work in your situation.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[TenBellie]

Sorry if it wasn't clear...not very good at explaining stuff!

I'll try again...

A fault is reported on 01/2/2014 07:23 (this data is the same cell A1)
The fault is cleared on 03/02/2014 15:00 (this data is in the same cell B1)

I need to be able to calculate the elapsed time diffrence between the two dates/times but the clock stops every day at 01:00 and restarts 08:00. Som eflts come in and out the same day at all times day or night

Hopefully that is better... it takes me an age every week working it out with a formula to cover all options....

 

[kjv1611]

When you say the clock stops between those times, so basically you just need a formula to always remove the time between 01:00 and 08:00 regardless of start/stop times, right? Because right now you're getting extra time added if they started it before 1:00, but finish after 8:00, but you don't want to include those 7 hours? Or am I thinking differently? If I'm way off, could you give a for instance of what would happen to cause the particular issue you're trying to solve for?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Regardless on whether I understand it or not, but sounds like maybe the DateDiff function might help, and you may need at least an IF clause to handle those instances when between 1:00 and 8:00..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

So you have this in your Excel:
[pre]
A B C
1 01/2/2014 07:23 03/02/2014 15:00 [red]???[/red]
[/pre]
(Assuming columns A and B are formatted as Dates)

What value do you expect to have in cell C1?
"elapsed time diffrence between the two dates/times" is not very clear.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[kjv1611]

Sorry - forgot to add a link on the DateDif function.. Chip Pearson has loads of good stuff on his site:

http://www.cpearson.com/excel/datedif.aspx

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Doing this kind of formula is very complicated. You must also add to the mix that there will be holidays and other unscheduled down time.

In my experience at 3 aerospace companies, where the granularity of scheduling was at the DAY level as opposed to the HOUR level as is your requirement, we have a maunfacturing day calendar, which specifies for each day of any year, the work days and non-work days.

So if you were to have an hourly calendar for each day of the week, indicating which day/hours are work and which day/hour are non-work, like this table as a structure Table named tCAL
[pre]
Date Hour WorkHrs
2/1/2014 0 1
2/1/2014 1 0
2/1/2014 2 0
2/1/2014 3 0
2/1/2014 4 0
2/1/2014 5 0
2/1/2014 6 0
2/1/2014 7 0
2/1/2014 8 1
2/1/2014 9 1
2/1/2014 10 1
2/1/2014 11 1
2/1/2014 12 1
2/1/2014 13 1
2/1/2014 14 1
2/1/2014 15 1
2/1/2014 16 1
2/1/2014 17 1
2/1/2014 18 1
2/1/2014 19 1
2/1/2014 20 1
2/1/2014 21 1
2/1/2014 22 1
2/1/2014 23 1
2/2/2014 0 1
2/2/2014 1 0
2/2/2014 2 0
2/2/2014 3 0
2/2/2014 4 0
2/2/2014 5 0
2/2/2014 6 0
2/2/2014 7 0
2/2/2014 8 1
2/2/2014 9 1
2/2/2014 10 1
2/2/2014 11 1
2/2/2014 12 1
2/2/2014 13 1
2/2/2014 14 1
2/2/2014 15 1
2/2/2014 16 1
2/2/2014 17 1
2/2/2014 18 1
2/2/2014 19 1
2/2/2014 20 1
2/2/2014 21 1
2/2/2014 22 1
2/2/2014 23 1
2/3/2014 0 1
2/3/2014 1 0
2/3/2014 2 0
2/3/2014 3 0
2/3/2014 4 0
2/3/2014 5 0
2/3/2014 6 0
2/3/2014 7 0
2/3/2014 8 1
2/3/2014 9 1
2/3/2014 10 1
2/3/2014 11 1
2/3/2014 12 1
2/3/2014 13 1
2/3/2014 14 1
2/3/2014 15 1
2/3/2014 16 1
2/3/2014 17 1
2/3/2014 18 1
2/3/2014 19 1
2/3/2014 20 1
2/3/2014 21 1
2/3/2014 22 1
2/3/2014 23 1
[/pre]

My formula:
[tt]
=SUMPRODUCT((tCAL[Date]+TIME(tCAL[Hour],0,0)>=A1)*(tCAL[Date]+TIME(tCAL[Hour],0,0)<=B1)*(TIME(tCAL[Workday],0,0)))
[/tt]

and my result is 1.75 days duration

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[bluedragon2]

Nice approach Skip, I wrote down the IF/THEN's of where the start and end times would be and the formula would get out of hand in a hurry.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[TenBellie]

That's great really appreciate the help works a treat sorry for my bad explanations

 

[kjv1611]

Skip, when you retire (if you didn't already), please tell me you'll still be posting on here in your downtime from retirement activities.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

I have 81 calendar days to my commencement.

Yes, I anticipate continuing to post in Tek-Tips.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

Worked out on a thoroughly debugged spreadsheet, no doubt.

 

[SkipVought]

Well I have a program that is a digital count down: days, hours, minutes, seconds

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]