Register
Log In
Unanswered
Home
June 25, 2016
Offline
Problem : Find the Total Hours Completed between 5 pm –7am
Let’s say we have this Shift Time Data Row A1,B1,C1,D1
Shift in time 6:00AM 7:00AM 4:00PM
Row A2,B2,C2,D2
Shift out time 4:00PM 5:00PM 10:00PM
i need to calculate the total shift hours that fall between 5 pm to 7 am.
If the shift starts at 7 am and ends at 5 pm. The total hours clocked (between 5 pm and 7 am) will be 0
If the shift starts at 6 am and ends at 4 pm. The total hours clocked (between 5 pm and 7 am) will be 1
If the shift starts at 4 pm and ends at 10 pm. The total hours clocked (between 5 pm and 7 am) will be 5
Dashboards
June 25, 2016
Offline
See if this is what you want
=IF(AND(HOUR(B1)=6,HOUR(B2)=16),1,IF(AND(HOUR(B1)=7,HOUR(B2)=17),0,IF(AND(HOUR(B1)=16,HOUR(B2)=22),5,0)))
New Member
June 25, 2016
Offline
How about this:
=IF(B1>B2,MIN(24-HOUR(B1),7)+MIN(HOUR(B2),7),MAX(7-HOUR(B1),0)+MAX(HOUR(B2)-17,0))
Cheers,
Dashboards
June 25, 2016
Offline
Another try =IFERROR(INDEX({0,1,5},(MATCH(HOUR(B1),{7,6,16},0)+MATCH(HOUR(B2),{17,16,22},0))/2),0)
Cheers
Most Users Ever Online: 57
Currently Online:
1 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Susan Booze
Rita Hrez
David Innes
preeti bhulabhai
Charles Klasek
Patrick Isedu
Emma Hayward
Vamseedhar G
NA NA
Max Neumann
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
