Checking Time blocks

[Gaveon]

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

 

[jasonb75]

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.