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

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 -

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 -

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 -

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

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.

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.