Difference in Days, Hours, Minutes and Seconds

P

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

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

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

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
 
P

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
 
J

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
 
J

Jarek Kujawa

oops! excellent!

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

;-)))
 
P

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
 
P

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
 
J

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
 
P

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top