I have to do a time sheet with 10 people on it, some work night and some work
days.The days are no problems but when it come to the night shift they start
at 1800 and finish the next day at 0600. I need a formula that will be able
to do both as they rotate between nights and days. I want it to do both
without having to re format everytime that they change from nights to days
Can anyone help

=MOD(end_time - start time, 1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"time formula" <time wrote in message
...
I have to do a time sheet with 10 people on it, some work night and some
work
days.The days are no problems but when it come to the night shift they
start
at 1800 and finish the next day at 0600. I need a formula that will be
able
to do both as they rotate between nights and days. I want it to do both
without having to re format everytime that they change from nights to days
Can anyone help

Hi there

Seems like you've ran into a common problem working with Excel dates & times.
In short (assuming you're not in to the nitty-gritty details) it's all about
formatting!

Excel, by default, handles all time calculations with a "base-unit" of 1 day
= 24hours.
When you need to do calculations where start-time is pre-midnight and
end-time is after-midnight Excel
will "think" that end-time < start-time. The result of "=end-time -
start-time" is therefore negative
which is rubbish (and would implicate that the employee had to pay for the
time he worked...).

You can handle this situation in many different ways. The one suggested by
Bob Phillips is beautyfull
and to give you options, I'll show you another:

A1: start-time
B1: end-time
C1: =B1-A1+(A1B1) (If you want to know HOW this work just ask for details)


This will work as long as the total time (C1) does not extend 24 hours (one
whole day). If that should happen
you will have to provide specific dates to Excel.

Hope this help!

I'm sorry for my poor english...

"time formula" skrev:

I have to do a time sheet with 10 people on it, some work night and some work
days.The days are no problems but when it come to the night shift they start
at 1800 and finish the next day at 0600. I need a formula that will be able
to do both as they rotate between nights and days. I want it to do both
without having to re format everytime that they change from nights to days
Can anyone help