I am creating a scheduler but am having problems figuring out the amount of
time between 6:00 PM and 12:30 AM. It would like it to return a figure of
6.5 hours.
Thanks.

Hi

With start time in A2 and end time in B2:
=B2-A2+(B2<A2)
and format as time.

When there is no working time which includes midnight, then you can have
this formula in simpler form:
=B2-A2


When using result of either formula in further calaculations, multiply it by
24. P.e. with result in C2, and having hourly fee 10$, you'll calculate
daily fee as
=C2*10*24


Arvi Laanemets


"Gene Mah" wrote in message
...
I am creating a scheduler but am having problems figuring out the amount
of
time between 6:00 PM and 12:30 AM. It would like it to return a figure
of
6.5 hours.
Thanks.

A couple of ways

=MOD(End-Start,1)*24

=(End-Start+(End<Start))*24

format as general or number (not time)





--
Regards,

Peo Sjoblom

(No private emails please)


"Gene Mah" wrote in message
...
I am creating a scheduler but am having problems figuring out the amount of
time between 6:00 PM and 12:30 AM. It would like it to return a figure
of
6.5 hours.
Thanks.

On Sat, 15 Oct 2005 09:37:31 -0700, "Gene Mah"
wrote:

I am creating a scheduler but am having problems figuring out the amount of
time between 6:00 PM and 12:30 AM. It would like it to return a figure of
6.5 hours.
Thanks.

So long as your time durations will always be less than 24 hours:

=(End-Start+(StartEnd)) * 24

Format the result as General, or Number with the desired number of decimals.








--ron

The problem is I have at least 30 employees and only a few will work until
12:30 AM. And it is not the same employees from week to week. I would like
a scheduler that I can just plug in the shift times and it automatically
figures out the amount of time worked. I have a worksheet made and could
attach it for you to review.
Thanks.
Gene


"Arvi Laanemets" wrote:

Hi

With start time in A2 and end time in B2:
=B2-A2+(B2<A2)
and format as time.

When there is no working time which includes midnight, then you can have
this formula in simpler form:
=B2-A2


When using result of either formula in further calaculations, multiply it by
24. P.e. with result in C2, and having hourly fee 10$, you'll calculate
daily fee as
=C2*10*24


Arvi Laanemets


"Gene Mah" wrote in message
...
I am creating a scheduler but am having problems figuring out the amount
of
time between 6:00 PM and 12:30 AM. It would like it to return a figure
of
6.5 hours.
Thanks.

This is a generic formula and it will work, just put in the shift times and
replace B2 with the cell you put in the end time and A2 with the start time

--
Regards,

Peo Sjoblom

(No private emails please)


"Gene Mah" wrote in message
...
The problem is I have at least 30 employees and only a few will work until
12:30 AM. And it is not the same employees from week to week. I would
like
a scheduler that I can just plug in the shift times and it automatically
figures out the amount of time worked. I have a worksheet made and could
attach it for you to review.
Thanks.
Gene


"Arvi Laanemets" wrote:

Hi

With start time in A2 and end time in B2:
=B2-A2+(B2<A2)
and format as time.

When there is no working time which includes midnight, then you can have
this formula in simpler form:
=B2-A2


When using result of either formula in further calaculations, multiply it
by
24. P.e. with result in C2, and having hourly fee 10$, you'll calculate
daily fee as
=C2*10*24


Arvi Laanemets


"Gene Mah" wrote in message
...
I am creating a scheduler but am having problems figuring out the
amount
of
time between 6:00 PM and 12:30 AM. It would like it to return a
figure
of
6.5 hours.
Thanks.

Here is an on-fly example how I would design such sheduler. In my example I
use ISO week definition (a week is always 7 days, the first week of year is
the one with 1st Thursday in it, 1st day of week is Monday)

Create a sheet SetUp
A1="Year"
B1 - enter the year your sheduler is meaned for.
A2="Lunch"
B2 - enter the length of lunchtime in format "h:mm"
Create named ranges (InsertNameDefine)
Year=$B$1
Lunch=$B$2

D1="Day"
E1="Week"
D2=IF(AND(YEAR(DATE(YearN,1,1+(ROW()-2)*7))=YearN,DATE(YearN,1,1+(ROW()-2)*7
)<=TODAY()),DATE(YearN,1,1+(ROW()-2)*7),"")
copy down for 53 rows
E2
=IF(D2="","",YearN+INT((D2-DATE(YEAR(D2-WEEKDAY(D2-1)+4),1,3)+WEEKDAY(DATE(Y
EAR(D2-WEEKDAY(D2-1)+4),1,3))+5)/7)/100-(WEEKDAY(D2)3))
E3
=IF(D3="","",YearN+INT((D3-DATE(YEAR(D3-WEEKDAY(D3-1)+4),1,3)+WEEKDAY(DATE(Y
EAR(D3-WEEKDAY(D3-1)+4),1,3))+5)/7)/100
copy E3 down for same range as formula in column D
Hide column D
Create named range
Weeks=OFFSET(SetUp!$E$1,1,,COUNT(SetUp!$E:$E),1)

Create a sheet Employees with table (headers on row 1)
ID, FirstName, LastName, ...
(you can have additional columns in table, but I'll continue with those 3).
The column ID must have unique values.
Create named ranges
Employee=OFFSET(Employees!$A$1,1,,COUNTA(Employees !$A:$A)-1,1)
EmployeesTbl=OFFSET(Employees!$A$1,1,,COUNTA(Emplo yees!$A:$A)-1,3)

Create a sheet Shedule with table
Date, EmployeeID, StartTime, EndTime, Lunch, Name, Week, Hours
For column B (EmployeeID) implement data validation list with source
=Employee
For column E (Lunch) implement data validation list with values "Yes","No"
F2=IF(B2="","",VLOOKUP(B2,EmployeesTbl,2,0)&"
"&VLOOKUP(B2,EmployeesTbl,3,0))
G2=IF(A2="","",YEAR(A2)-(YEAR(A2-4)<YEAR(A2))+INT((A2-DATE(YEAR(A2-WEEKDAY(A
2-1)+4),1,3)+WEEKDAY(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,3))+5)/7)/100)
H2=IF(OR(C2="",D2=""),"",D2-C2+(D2<C2)-(E2="Yes")*Lunch)
Copy F2:H2 down as much as you thin you need.
Create named ranges
ShedEmpl=OFFSET(Shedule!$B$1,1,,COUNT(Shedule!$A:$ A),1)
ShedWeek=OFFSET(Shedule!$G$1,1,,COUNT(Shedule!$A:$ A),1)
ShedHours=OFFSET(Shedule!$H$1,1,,COUNT(Shedule!$A: $A),1)

Create a sheet WeeklyRep
A1="Week:"
Implement data validation list to cell B1, with cource
=Weeks
Select a week.
A3="EmployeeID"
B3="EmployeeName"
C3="Hours"
A4=IF(ISERROR(INDEX(Employee,ROW()-3)),"",INDEX(Employee,ROW()-3))
B4=IF(A4="","",VLOOKUP(A4,EmployeesTbl,2,0)&"
"&VLOOKUP(A4,EmployeesTbl,3,0))
C4=IF(A4="","",SUMPRODUCT(--(ShedEmpl=A4),--(ShedWeek=RepWeek),ShedHours))
Format C4 as "[h]:mm"
Copy A4:C4 down at least for so much rows as you have employees.

It's done!


Arvi Laanemets


"Gene Mah" wrote in message
...
The problem is I have at least 30 employees and only a few will work until
12:30 AM. And it is not the same employees from week to week. I would
like
a scheduler that I can just plug in the shift times and it automatically
figures out the amount of time worked. I have a worksheet made and could
attach it for you to review.
Thanks.
Gene


"Arvi Laanemets" wrote:

Hi

With start time in A2 and end time in B2:
=B2-A2+(B2<A2)
and format as time.

When there is no working time which includes midnight, then you can have
this formula in simpler form:
=B2-A2


When using result of either formula in further calaculations, multiply
it by
24. P.e. with result in C2, and having hourly fee 10$, you'll calculate
daily fee as
=C2*10*24


Arvi Laanemets


"Gene Mah" wrote in message
...
I am creating a scheduler but am having problems figuring out the
amount
of
time between 6:00 PM and 12:30 AM. It would like it to return a
figure
of
6.5 hours.
Thanks.