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

G

Gene Mah

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

Arvi Laanemets

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
 
P

Peo Sjoblom

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)
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
G

Gene Mah

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
 
P

Peo Sjoblom

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)
 
A

Arvi Laanemets

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
 
Top