How do I find the amount of hours between 6:00 PM to 12:30 AM?

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>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"
<[email protected]> 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+(Start>End)) * 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 (Insert>Name>Define)
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(Employees!$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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> >
> >
> >