Checking Time blocks

Gaveon

New Member
Joined
Aug 11, 2016
Messages
3
Hello,

im trying to create a check for a bunch of time blocks for staff ID

Staff Start End Total Time
1111 8:30 9:30 0:30 "Under 1 hour"
1111 10:00 10:30 0:30 "OK"
1111 10:30 11:00 0:30 "OK"
1111 12:00 13:00 1:00 "OK"

If staff work a block less than an hour they will be paid remaining time
staff work with clients in that time - some clients have a couple service back to back such as row 2 & 3

i want to create a check where if time is less than 1 hour it will alert me
but ignore records that have a connecting time

I'm guessing i will just need a complex IF statement

Thank you for an help
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
8,099
Office Version
2019
Platform
Windows
Starting off fairly simple, see if this works (assuming total time in column D)

=IF(OR(D2>=(1/24),C2=B3,C1=B2),"OK","Under 1 hour")

This will not pick up blocks that are under 1 hour, i.e. 2 back to back rows of 15 minutes each no additional row(s) to increase it further. If individual rows of less than 30 minutes are possible then this will need to be allowed for with something a little more complex.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,408
Messages
5,414,228
Members
403,520
Latest member
Pineappleman

This Week's Hot Topics

Top