Tracking Date/Actual Working Time Elapsed
 

i have obtained a solution from this Discussion Board which more or less does
what i require.

I have two dates the start date and time (A1) and the end date and time (B1)

Our standard working day is 9:00 to 17:00 with lunch from 12:30 to 13:30
monday to friday.

I need to find the time spent working on a particular project in the format
[h]:mm between the values set out in A1 and B1

neither of the two soultions i have found allow for lunch, bearing in mind
that some tasks may start 30min before lunch and then be finished 15min after
it would only take 45 min.

the equations i refer to were posted by Roger Glover and daddylonglegs are;

=(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+NETWORKDAYS(A1,B1,0)-2)*TIME(9,0,0)

In which i assume i was ok to change the 8:00 value to 9:00 and the time
multiplier from 9 down to 8 to reflect our working pattern.

and a simplified version

=MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,0)-1*"09:00"

If someone could help me include a lunch period without it causing an error
if someone was to have the start or finish time within the lunch period i
would be most thankful.

Tracking Date/Actual Working Time Elapsed
 

Assuming A1 is the start time, B1 is the end time, and 1 hour is allowed for
lunch, just do something like

=((B1-A1)*24)-1

If B1 is 5PM and A1 is 8AM, then ((B1-A1)*24)-1 = 9 - 1 = 8 hours.

Dave
--
Brevity is the soul of wit.


"Jive" wrote:

i have obtained a solution from this Discussion Board which more or less does
what i require.

I have two dates the start date and time (A1) and the end date and time (B1)

Our standard working day is 9:00 to 17:00 with lunch from 12:30 to 13:30
monday to friday.

I need to find the time spent working on a particular project in the format
[h]:mm between the values set out in A1 and B1

neither of the two soultions i have found allow for lunch, bearing in mind
that some tasks may start 30min before lunch and then be finished 15min after
it would only take 45 min.

the equations i refer to were posted by Roger Glover and daddylonglegs are;

=(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+NETWORKDAYS(A1,B1,0)-2)*TIME(9,0,0)

In which i assume i was ok to change the 8:00 value to 9:00 and the time
multiplier from 9 down to 8 to reflect our working pattern.

and a simplified version

=MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,0)-1*"09:00"

If someone could help me include a lunch period without it causing an error
if someone was to have the start or finish time within the lunch period i
would be most thankful.