• 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.

Sum time between two dates/times that matches certain criteria

beepbeep

beepbeep

New Member
Hello all,

This is my first post to the forum, but it's a problem that has been bugging me for months (!)

I would like to know how I go about writing a formula that will give me the number of seconds between two dates/times - but that meet certain criteria (i.e. summing only a subset of the total time between the two dates/times).

In my sample workbook that I have attached, I have a number of dates when a fire appliance was unavailable. I know the total unavailability of the fire appliance on each occasion, but would like to identify the total number of seconds between the two dates/times that fell within 9am-6pm (or 17:59:59 to be precise).

I've deliberately chosen some dates and times in the attached sample, that fall both inside and outside (and half-in, half-out) the 9am-6pm range.

Any help would be gratefully received!!
 

Attachments

  • SampleUnavailability.xlsx
    9.8 KB · Views: 12
Please try this is in D2, hopefully it works:

Code: 
=ABS(IF((B2-QUOTIENT(A2,1))<=0.75,B2-QUOTIENT(B2,1),0.75)-IF((A2-QUOTIENT(A2,1))>0.375,A2-QUOTIENT(A2,1),0.375))
 
Faseeh,

First of all, many thanks for such a swift reply!

I have tried this out as requested and we are almost there! I've attached an update which shows what I expected the results to be- through traditional adding up with the fingers and thumbs - and there are a few instances where the formula isn't showing what I think to be the correct answer.

There are two occurrences where I expected the answer to be 0, as the start and finish times were outside of the 9am-6pm range (D4 & D8 respectively).

D5 & D6 don't seem to take into account the additional time in column B that falls within the 9am-6pm range for the following day (particulaly for D6 as the end time is greater than a 24hr period).

I am determined to get my head around your very succinct formula and use of quotients etc. in the meantime, so I understand what is going on..... but I might end up asking for "why" it works if I draw a complete blank! :)

Any further help in correcting where the time spreads over more than 24 hours would be much appreciated, thanks!
 

Attachments

  • SampleUnavailability2.xlsx
    12 KB · Views: 7
Hi Luke M,

That is absolutely spot on thanks! Works like a charm.

I'm just trying to fully understand the formula and how it works
So the first line gives the total number of 'working hours' across the time-range and then the 2nd and 3rd parts deduct the hours that don't fall within the 'working day'

Can you walk me through the use of the Max/Min parts? I'm trying to decipher what is going on here exactly.

Thanks to all who have helped!
 
Sure thing.
For our example, we'll say that we worked from 7:00am on Saturday to 4pm on the same Saturday.
As you said, first line figures out how many days we are working with (1), and starts with assumption that it's a full working day (9 hours). Side note: While we could have left everything as decimals, it's much easier to understand if we convert to hours, and then just divide by 24 at the end. But moving on.

We need to know how many, if any, of the 9 working hours on both the StartDate and EndDate we need to take away. Let's look at middle line:

MAX(0,MIN(9,18-MOD(EndDate,1)*24))
First, the MOD function gives us just the decimal portion of the date. I then multiply by 24 to convert to hours. So, this would give us the number 16 (aka, 4pm). Our cut-off for the working day is 6 pm (aka 18). We want to know how many hours, if any, are in the gap between when we stopped working and the end of work day, so we subtract. In our example, this gives us the number 2. If we had stopped before the work day began, the number would be > 9, but the MIN function will prevent us from exceeding 9. If we had past 6pm, our number would by a negative number, and the MAX function would catch us.
So, if we stop before 9 am:
18-MOD() produces a number > 9, the MIN function limits us to 9, and we subtract 9 hours from total.
If we stop after 6 pm:
18-MOD() produces a negative number, the MAX function limits us to 0, so we don't subtract from total.
In we stop between 9 am and 6 pm:
18-MOD() produces a number somewhere between 0-9, and we subtract that from the total.

In our example, we'll need to subtract 2 hours.

For the StartDate, it's similar logic, but this time we'll do MOD()-9, since we're looking for a start point, not an end point. In our example, we started before 9am, so the subtraction produces a negative number, which gets reduced to 0, and so we'll subtract 0 hours.

Final math then is:
=(9-2-0)
This gives us 7 hours (correct!). We then divide by 24 to convert back to decimals, so that XL can format it correctly.

Does that help?
 
Luke M said:
Sure thing.
For our example, we'll say that we worked from 7:00am on Saturday to 4pm on the same Saturday.
As you said, first line figures out how many days we are working with (1), and starts with assumption that it's a full working day (9 hours). Side note: While we could have left everything as decimals, it's much easier to understand if we convert to hours, and then just divide by 24 at the end. But moving on.

We need to know how many, if any, of the 9 working hours on both the StartDate and EndDate we need to take away. Let's look at middle line:

MAX(0,MIN(9,18-MOD(EndDate,1)*24))
First, the MOD function gives us just the decimal portion of the date. I then multiply by 24 to convert to hours. So, this would give us the number 16 (aka, 4pm). Our cut-off for the working day is 6 pm (aka 18). We want to know how many hours, if any, are in the gap between when we stopped working and the end of work day, so we subtract. In our example, this gives us the number 2. If we had stopped before the work day began, the number would be > 9, but the MIN function will prevent us from exceeding 9. If we had past 6pm, our number would by a negative number, and the MAX function would catch us.
So, if we stop before 9 am:
18-MOD() produces a number > 9, the MIN function limits us to 9, and we subtract 9 hours from total.
If we stop after 6 pm:
18-MOD() produces a negative number, the MAX function limits us to 0, so we don't subtract from total.
In we stop between 9 am and 6 pm:
18-MOD() produces a number somewhere between 0-9, and we subtract that from the total.

In our example, we'll need to subtract 2 hours.

For the StartDate, it's similar logic, but this time we'll do MOD()-9, since we're looking for a start point, not an end point. In our example, we started before 9am, so the subtraction produces a negative number, which gets reduced to 0, and so we'll subtract 0 hours.

Final math then is:
=(9-2-0)
This gives us 7 hours (correct!). We then divide by 24 to convert back to decimals, so that XL can format it correctly.

Does that help?
Click to expand...

Hi Luke,

I've been expanding this formula to show the unavailability by hour of day (as opposed to between 9am-6pm). However, I've come into a bit of a pickle (see attached example). The formulas seem to work whenever the start date is the same as the end date - but whenever it spans across more than a day, I get spurious results (highlighted yellow). I think the first and last columns (midnight to 1am & 11pm-midnight) may also be causing an issue where we are dealing with 'midnight' which is '0' in excel terms... any help on rectifying the issue would be most helpful!

Many thanks!!
 

Attachments

  • SampleUnavailability3.xlsx
    16.3 KB · Views: 12
I feel the previous formula led to confusion due to StartTime = Duration. Hopefully this clears it up a little.
=(((INT(endDate)-INT(StartDate)+1)*Duration)-
MAX(0,MIN(Duration,EndTime-MOD(endDate,1)*24))-
MAX(0,MIN(Duration,MOD(StartDate,1)*24-StartTime)))/24
Where Duration, EndTime, and StartTime are in hours units (so, if work day was 1.5 hours, Duration should be 1.5).

Formula in E3 then is:
=(((INT($B3)-INT($A3)+1)*1)-
MAX(0,MIN(1,COLUMN(A$1)-MOD($B3,1)*24))-
MAX(0,MIN(1,MOD($A3,1)*24-(COLUMN(A$1)-1))))/24

Copy this to the right and down. Note that the COLUMN function is there so we don't have to manually adjust for the different time blocks (1-2, 2-3, 3-4, etc.)
 
Thanks for the clarification Luke! I managed to suss out the start time = duration 'confusion' and ALMOST had it this afternoon, but had forgotten to change one of the variables!

Thanks for the tip regarding COLUMN too - I knew there must have been something and I weep at the amount of time wasted over the years rather than finding out a better way! You are a true excel ninja :)
 
Hi,

Correct me if I did not understood the question asked in comment number #8.

The new formula given by @Luke M in comment #9 is returning values. But shouldn't be sum of values in column E:AB be equal to column D value?



Regards,
 
Somendra Misra said:
Hi,

Correct me if I did not understood the question asked in comment number #8.

The new formula given by @Luke M in comment #9 is returning values. But shouldn't be sum of values in column E:AB be equal to column D value?
Click to expand...
Not quite. Col D is only summing the time between 9 am and 6 pm. This formula is what was originally written. Now we are looking at the whole 24 hour period and splitting out the time.
 
Yeah @Luke M I just reaslised that after posting the comment.
But can you just check with these start & end Date - time Respectively, the values which are coming are OK or not?


29-10-2012 09:00:11 30-10-2012 17:00:12


Regards,
 
@Somendra Misra
From what I've been told by OP, yes. The total time between those dates is 1 day + 8 hours + 1 second. If you SUM E:AB, that is the exact value you get, and we can see that some cells have 2 hours blocked off. This may or may not be what OP desires...I was treating it like we are sorting out the hours into "bins".
 
Luke M said:
@Somendra Misra
From what I've been told by OP, yes. The total time between those dates is 1 day + 8 hours + 1 second. If you SUM E:AB, that is the exact value you get, and we can see that some cells have 2 hours blocked off. This may or may not be what OP desires...I was treating it like we are sorting out the hours into "bins".
Click to expand...

@Somendra Misra
Hi there - I can confirm that Luke is spot on - it's doing exactly what it should be doing - effectively sorting the hours into 'bins' - bit like a histogram/frequency diagram if you remember those when you did Maths at school :)
 
@beepbeep
Hi, Road-runner!
I don't know about Somendra Misra but when Luke M was in age of going to school, they were not invented yet; even less Maths. :p
Regards!
 
@beepbeep

Thanks for taking me to school days. :)

But don't you think a value like 3:00:00 in the interval of 00:00 - 01:00 is bit confusing and one has to go back to see the dates and confirm it. The idea of distribution of total hours look good for one day 24 hours but if it beyond one day I think the distribuiton should be over days and not hours.

@SirJB7 .. I can't imagine the age of @Luke M . When he was born somewhere near to 10~11 century. :)

Just a thought.
Regards,
 
@Somendra Misra
Taking folks back to school days is often a bit of a gamble - depending very much as to how they got on!

I completely understand your reasoning - but distributing the values in this way is only 1 of a number of ways that I would look at these figures in this scenario.
In the dummy-example I provided, it allows a very simple chart to be plotted which would depict, over a 24hr period, which time of day have fire appliances been unavailable the most (say, due to staffing issues). The actual numbers are almost secondary compared to the 'shape' of the graph. The chart may inform me that, say, a particular fire appliance is most often unavailable at tea-time - I'm from England :) - this would allow me to interrogate further and try to understand why etc. and act accordingly... hope that makes sense :)
 
I should apparently conduct a poll...I'm curious to see how old people think I am! :DD
 
Herbert

Welcome to the Chandoo.org Forums

This post doesn't appear related to the thread topic
So Please start new threads when posting,

Is there a question related to the post or is it instructional?
You should either ask a question or be more descriptive about what you are telling us

Please take the time to read a few rules about how we operate here
http://chandoo.org/forum/threads/new-users-please-read.294/
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Hui...

Herbert Seidenberg said:
Excel 2010, Table, PivotTable
With multiple fire periods per date, covering midnight.
http://www.mediafire.com/view/08areq8idepwpap/03_13_14.xlsm
Click to expand...
 
Hui
Might not be related in the sense that I did not use the date/time of the OP,
but my own 3 sequential date/times (in US format) to illustrate the midnight problem.
Also added more descriptive graphics.
Herb
 
You must log in or register to reply here.
Back
Top