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.

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.
--
Gary''s Student - gsnu200826

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.



"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200826

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

On Jan 15, 10:38*am, Gary''s Student
wrote:
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.
--
Gary''s Student - gsnu200826

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

On Jan 15, 10:47*am, Pumpkin
wrote:
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.



"Gary''s Student" wrote:
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..
--
Gary''s Student - gsnu200826- Hide quoted text -

- Show quoted text -

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

On 15 Sty, 10:49, Pumpkin wrote:
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.

oops! excellent!

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

;-)))


On 15 Sty, 12:02, Pete_UK wrote:
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

On Jan 15, 10:47*am, Pumpkin
wrote:



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.

"Gary''s Student" wrote:
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.
--
Gary''s Student - gsnu200826- Hide quoted text -

- Show quoted text -- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

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

On Jan 15, 11:20*am, Jarek Kujawa wrote:
oops! excellent!

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

;-)))

On 15 Sty, 12:02, Pete_UK wrote:



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

On Jan 15, 10:47*am, Pumpkin
wrote:

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.

"Gary''s Student" wrote:
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.
--
Gary''s Student - gsnu200826- Hide quoted text -

- Show quoted text -- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -- Hide quoted text -

- Show quoted text -

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" wrote:

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

On Jan 15, 10:47 am, Pumpkin
wrote:
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.



"Gary''s Student" wrote:
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..
--
Gary''s Student - gsnu200826- Hide quoted text -

- Show quoted text -

You're welcome.

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

Pete

On Jan 15, 11:58*am, Pumpkin
wrote:
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" wrote:
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

On Jan 15, 10:47 am, Pumpkin
wrote:
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.

"Gary''s Student" wrote:
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..
--
Gary''s Student - gsnu200826- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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


On 15 Sty, 13:06, Pete_UK wrote:
You're welcome.

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

Pete

On Jan 15, 11:58*am, Pumpkin
wrote:



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" wrote:
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

On Jan 15, 10:47 am, Pumpkin
wrote:
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.

"Gary''s Student" wrote:
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..
--
Gary''s Student - gsnu200826- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

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


On 15 Sty, 12:56, Pete_UK wrote:
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

On Jan 15, 11:20Â*am, Jarek Kujawa wrote:



oops! excellent!

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

;-)))

On 15 Sty, 12:02, Pete_UK wrote:

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

On Jan 15, 10:47Â*am, Pumpkin
wrote:

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.

"Gary''s Student" wrote:
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.
--
Gary''s Student - gsnu200826- Hide quoted text -

- Show quoted text -- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -- Hide quoted text -

- Show quoted text -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

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



On 15 Sty, 12:58, Pumpkin wrote:
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" wrote:
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

On Jan 15, 10:47 am, Pumpkin
wrote:
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.

"Gary''s Student" wrote:
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..
--
Gary''s Student - gsnu200826- Hide quoted text -

- Show quoted text -- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

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" wrote:

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



On 15 Sty, 12:58, Pumpkin wrote:
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" wrote:
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

On Jan 15, 10:47 am, Pumpkin
wrote:
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.

"Gary''s Student" wrote:
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..
--
Gary''s Student - gsnu200826- Hide quoted text -

- Show quoted text -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

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


On 15 Sty, 17:00, Pumpkin wrote:
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" wrote:
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

On 15 Sty, 12:58, Pumpkin wrote:
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" wrote:
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

On Jan 15, 10:47 am, Pumpkin
wrote:
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..

"Gary''s Student" wrote:
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..
--
Gary''s Student - gsnu200826- Hide quoted text -

- Show quoted text -- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

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

"Jarek Kujawa" wrote:

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

On 15 Sty, 10:49, Pumpkin wrote:
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.