On Mon, 21 Apr 2008 00:35:01 -0700, Marty
wrote:

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

"Lars-Åke Aspelin" wrote:

On Sun, 20 Apr 2008 02:26:00 -0700, Marty
wrote:

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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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





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




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