time difference

[oldLearner57]

hi community

any help much appreciated on this task.....

D E F
G
4 Shift Start Time
Dismiss Time
5 1 08:30
18:15
6 2 09:30
19:15
7 3 10:30
20:15
8 Shift Sign In Sign Out
9 08:30 18:45
10 09:00 18:57:38
11 10:01 20:16

referring to the above scenario, how can I set the Shift in cell D9 - D11,
where I set the criteria as

a) if sign-in time is before 0900, it will be consider "Shift 1"
b) if sign-in time is between 0900 - 0955, it will be consider "Shift 2"
c) if sign-in time is between 1000 - 1100, it will be "Shift 3"

base on Col F (Start Time)

thanks community for the help given

 

[Roger Govier]

Hi
One way
=IF(F1="","",IF(F1<TIME(9,0,0),"Shift 1",IF(F1<TIME(9,59,0),"Shift 2",
IF(F1<TIME(11,0,0),"Shift 3","N/A"))))

 

[oldLearner57]

thanks & appreciated ! Roger Govier , i used the =time(hr,min,sec) format,
it works.

thanks community as well

 

[Sandy Mann]

Try:

=IF(F2,CHOOSE(MIN(MAX(FLOOR(F2,"1:00")*24-7,1),3),"Shift 1","Shift 2","Shift
3"),"")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk