calculating hours work has taken

[Marty]

Using the following;
C1 is formatted in dd/mm/yyyyy h:mm entered value is 15/4/2008 8:00. This is
commencement of work.
D1 is formatted in dd/mm/yyy h:mm entered value is 18/4/2008 10:00. This is
end of work.
In E1 I would like to calculate the total hours worked giving the answer in
days and hours. Note the working day is 7 hours 30 min long.
Thanks in advance

 

[Rick Rothstein \(MVP - VB\)]

Because you mentioned the length of the day, I'm not completely sure what
answer you are looking for (it would have helped if you told us the answer
you expected for the example you gave). I'll go with what I think is the
obvious (length of the day is immaterial... from the 15th to the 18th is 3
days, period, and the number of hours is difference from an assumed start
time of 8:00am to the given end time, assumed to be no more than 7.5). If
that is the case, then try this formula...

=TEXT(D1-C1,"d ""days"" h ""hours""")

If you wanted something else, or more (account for overtime for example),
then give us some more details.

Rick

 

[Marty]

Thanks Rick

Would it help if I mentioned the working hours of a day are 07:30 to 16:00,
minus 1:00 lunch is a total of 7:30 that is possible to work in any given
day. Therefore it would be correct to say if the job took at total of 8:35 to
complete then the answer I was looking for is 1 day, 1 hour and 5 minutes.

Many thanks

 

[Lars-Åke Aspelin]

Thanks Rick

Would it help if I mentioned the working hours of a day are 07:30 to 16:00,
minus 1:00 lunch is a total of 7:30 that is possible to work in any given
day. Therefore it would be correct to say if the job took at total of 8:35 to
complete then the answer I was looking for is 1 day, 1 hour and 5 minutes.

Many thanks

Still not enough information given.
If we don't know when the lunch is, we can't tell e.g. if a work
started 10:30 and ended 12:30 took 1 hour (if lunch is 11-12) or 2
hours (if lunch is 13-14)

Lars-Åke

 

[Marty]

Lunch is between 12 and 1 Monday to Friday.
No work on Saturday and Sunday.
(wish I had a job that worked those hours

 

[Lars-Åke Aspelin]

Lunch is between 12 and 1 Monday to Friday.
No work on Saturday and Sunday.
(wish I had a job that worked those hours

Try this:
(Start time is in cell A1 and end time in cell A2.)

=DAY(A2)-DAY(A1)-2*(INT((DAY(A2)-DAY(A1))/7)+(WEEKDAY(A2)<
WEEKDAY(A1)))-(MOD(A2,1)<MOD(A1,1)) & " days and " & (TEXT(
IF(MOD(A2,1)>=MOD(A1,1),MOD(A2,1)-MOD(A1,1),8.5/24+(MOD(A2,1)-
MOD(A1,1)))-1/24*(OR(AND(MOD(A1,1)<0.5,MOD(A2,1)>0.5),AND(MOD(A2,1)<
MOD(A1,1),OR(MOD(A1,1)<=0.5,MOD(A2,1)>0.5)))), "h:mm"))& " hours:mins"

This formula is made under the assumption that no start or end of work
can take place during lunch hour or outside working hours, and that
the work starts before it ends

This is how it works:

The first part calculates the difference in day by subtracting 2 days
for each complete week. Also subtrackt 2 days if the end date is on an
earlier weekday than the starting date. Finally subtract one day if
the end time is earlier on the end day than the starting time on the
start day. This give the number of (whole) days.

The second part calculates the additional hours by subtracting the
start time within the start day from the end time within the end day.
Add 8.5 hours to get a positive value if the end time is earlier than
the start time.
Finally subtract 1 hour, i.e. 1/24 days if there is a lunch break in
the interval. That happens either if the start time is before noon
(0.5) and the end time if after noon or if the start time is after
the end time and either the start time is before noon or the end time
is after noon.

Lars-Åke

 

[T. Valko]

Note that using this method the days will roll over after 31 and there's no
way to stop it. [d] doesn't work.

 

[Marty]

Sort of works. However I entered a start time of 08:00 and a end time of
20:00. Minus the 1 hour lunch it returned a value of 11 hours. normally this
would be correct however the working day is only 7:30 long. The answer I was
hoping for is 1 day and 3 hours and 30 minutes

 

[Rick Rothstein \(MVP - VB\)]

Your last post raises a question... if you can have more than 7.5 hours
worked in a single day, how would we know how many hours to assign per day
for a multi-day span like you gave us in your first posting? For example, in
your first posting, you said...

C1 is formatted in dd/mm/yyyyy h:mm entered value is 15/4/2008 8:00.
This is commencement of work.

D1 is formatted in dd/mm/yyy h:mm entered value is 18/4/2008 10:00.
This is end of work.

How do we know an 11-hour day (like the one you just proposed in your last
posting) was not worked on, say, the 17th?

Rick

 

[Lars-Åke Aspelin]

Sort of works. However I entered a start time of 08:00 and a end time of
20:00. Minus the 1 hour lunch it returned a value of 11 hours. normally this
would be correct however the working day is only 7:30 long. The answer I was
hoping for is 1 day and 3 hours and 30 minutes

How can you have an end time of 20:00 if working hours end at 16:00 as
you mentioned in an earlier post???

Lars-Åke