time difference
 

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


--
oldLearner57

time difference
 

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

--
Regards
Roger Govier



"oldLearner57" wrote in message
...
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 :)


--
oldLearner57

time difference
 

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

thanks community as well :)
--
oldLearner57


"Roger Govier" wrote:

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

--
Regards
Roger Govier



"oldLearner57" wrote in message
...
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 :)


--
oldLearner57

time difference
 

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


Replace @mailinator.com with @tiscali.co.uk


"oldLearner57" wrote in message
...
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 :)


--
oldLearner57