Difference in Days, Hours, Minutes and Seconds

[Pumpkin]

Hello,

Really struggling with this one:
Im trying to collate information from two set dates in DD:MM:YYYY:HH:MM:SS
as some data is only seconds and some days.
I have a original date of the action and then the date of the second action,
I need the difference.
I also need the formula to only collate difference between a starting time
of 07:30 and a finishing time of 18:00 across five working days Mon-Fri
Example:
26/11/2007 16:41:47 to 27/11/2007 09:56:24
At the minute this is showing as 17:14:37 difference, but it should be
03:44:37.

Can you help me please.

 

[Gary''s Student]

Re-check you math. Excel is correct.

After all, from 4 PM on day 1 to 9 AM on the next day is about 17 hours.

 

[Pumpkin]

Hmm,

But if the working day ends at 18:00 on the first data, and the starts at
07:30 on the next day the EXCEL is collating the time in between. Not the
time upto 18:00 on the first data then from 07:30 on the next day.

 

[Pete_UK]

Yes, I thought that at first, and then realised that the OP wants
"working" time, i.e. to exclude time between 18:00 and 7:30 weekdays
and all weekends.

Pete

 

[Pete_UK]

If they are on consecutive dates, as your example shows, then you can
use this:

=INT(A1)+0.75-A1+B1-INT(B1)-7.5/24

where A1 is the start date/time and B1 is the end date/time.

Hope this helps.

Pete

 

[Jarek Kujawa]

A1=26/11/2007 16:41:47
B1 = 27/11/2007 09:56:24

(in this particular case) the formula:
=B1-DATE(YEAR(B1),MONTH(B1);DAY(B1))-7.5/24+DATE(YEAR(A1);MONTH(A1);DAY
(A1))+18/24-A1

gives 03:44:37 as a result

-7.5/24 extracts 07:30 as start time every working day
+18/24 works as 18:00 as end of working day

looks complicated but I was not able to come up with anything simpler

HIH

 

[Jarek Kujawa]

oops! excellent!

this the SIMPLER formula I was not able to come up with

;-)))

 

[Pete_UK]

Thanks, Jarek, but it only works for consecutive days. We need to add
to it the number of full working days (excluding weekends) between the
dates times working hours per day (11.5) for a more complete solution.
I've not sussed that out yet.

Pete

 

[Pumpkin]

This works great. Thanks.

Can this be manipulated so that it calculates dates past the consecutive
day? Some dates are upto 5 days apart.

Pumpkin

 

[Pete_UK]

You're welcome.

See my reply to Jarek - I'll have another look at it after lunch.

Pete

 

[Jarek Kujawa]

yep, exactly
this is why I wrote "(in this particular case) "...

 

[Jarek Kujawa]

yep, exactly
this is why I wrote "(in this particular case) "...

 

[Jarek Kujawa]

this formula does not work for Saturdays and Sundays but I hope it
does not have to
;-)))

=((INT(B1-A1)-(WEEKNUM(B1,2)-WEEKNUM(A1,2))*2)*10.5/24)+B1-INT
(B1)-7.5/24+INT(A1)+18/24-A1

I'll try to work on it

 

[Pumpkin]

Thanks guys,

cant seem to get this one working though, but I have got the same day and
consecutive day data.
Now the next problem lies with trying to get an average amount of
dd:hh:mm:ss per line of data.
I have tried just doing the obvious by dividing my amount of records by the
accumalted dd:hh:mm:ss from the data I have combined, but I cannot seem to
get the right figures (or the figure are way higher then they should be)

Example:

1324 lines of data.
24:24:15:25 time accumalted
Answer i got was 22:04:52:39 (Cant be right)

I did try to convert the accumalated time to hours by *24, this gave a
result of 597.26 hours. Once i divided the lines by this i got 2.2hrs, Does
this seem correct, I seem to have got myself mixed up changing the formats
around.


Again any help would be great.

Pumpkin

 

[Jarek Kujawa]

pls provide a little explanation
what is "24:24:15:25" meant to be?
24 days, 24 hours, 15 mins and 25 secs?
cannot figure it out

 

[Pumpkin]

Thanks for all your help guys!!!
Really helped me out)