Using nested if in attendance tracker- need help

[hisyedaduil]

i am preparing an attendance tracker in excel 2007.
In a company the login time is 10 am. and as per the time their status should get updated
10:00am - 10:15am - late
10:15am - 11:30am - Permission
11:30am - 3:00pm - halfday
3:00pm onward - fullday off or leave

i have been able to use 2 nested ifs but not able to do it more than that. please help i am willing to share the excel sheet. if anyone is willing to help.

can you help? would you want to try?. please relpy

People if you have a good attendance tracker please share adildangerboy_2002@yahoo.co.in

 

[VoG]

Try

=IF(A1>="15:00"+0,"Leave",IF(A1>="11:30"+0,"Half day",IF(A1>="10:15"+0,"Permission",IF(A1>"10:00"+0,"Late",""))))

 

[hisyedaduil]

ok lets try

 

[Weaver]

create a table elsewhere that looks like this

Excel Workbook
	G	H
1	0	OK
2	10:00	Late
3	10:15	Permission
4	11:30	Half Day
5	15:00	Full Day or Leave
Sheet9

Excel 2007



Then, assuming your times start in A1

=LOOKUP(A1,$G$1:$H$5)

in B1 and copy down

HTH

 

[hisyedaduil]

VOG work perfectly fine. super duper thanks.

Thanks for the second reply Weaver. will try your method aswell

 

[hisyedaduil]

Weaver does not work fine will all entries.

10:46 OK (it should be Permission-which is incorrect)
11:11 Permission
10:21 Permission
10:04 Permission (it should be late - which is incorrect)
10:14 Late

can you improvise on it?

 

[Weaver]

Could you please have a look at your setup.

Here are my test results

Excel Workbook
	A	B
1	time	formula
2	09:57	OK
3	09:58	OK
4	09:59	OK
5	10:00	Late
6	10:01	Late
7	10:02	Late
8	10:03	Late
9	10:04	Late
10	10:05	Late
11	10:14	Late
12	10:15	Permission
13	10:55	Permission
14	11:00	Permission
15	11:28	Permission
16	11:29	Permission
17	11:30	Half Day
18	11:31	Half Day
19	11:32	Half Day
20	14:58	Half Day
21	14:59	Half Day
22	15:00	Full Day or Leave
23	15:01	Full Day or Leave
24	15:05	Full Day or Leave
25	15:06	Full Day or Leave
26	10:46	Permission
27	11:11	Permission
28	10:21	Permission
29	10:04	Late
30	10:14	Late
Sheet9

Excel 2007

Cell Formulas
Range	Formula
B2	=LOOKUP(A2,$G$1:$H$5)
B3	=LOOKUP(A3,$G$1:$H$5)
B4	=LOOKUP(A4,$G$1:$H$5)
B5	=LOOKUP(A5,$G$1:$H$5)
B6	=LOOKUP(A6,$G$1:$H$5)
B7	=LOOKUP(A7,$G$1:$H$5)
B8	=LOOKUP(A8,$G$1:$H$5)
B9	=LOOKUP(A9,$G$1:$H$5)
B10	=LOOKUP(A10,$G$1:$H$5)
B11	=LOOKUP(A11,$G$1:$H$5)
B12	=LOOKUP(A12,$G$1:$H$5)
B13	=LOOKUP(A13,$G$1:$H$5)
B14	=LOOKUP(A14,$G$1:$H$5)
B15	=LOOKUP(A15,$G$1:$H$5)
B16	=LOOKUP(A16,$G$1:$H$5)
B17	=LOOKUP(A17,$G$1:$H$5)
B18	=LOOKUP(A18,$G$1:$H$5)
B19	=LOOKUP(A19,$G$1:$H$5)
B20	=LOOKUP(A20,$G$1:$H$5)
B21	=LOOKUP(A21,$G$1:$H$5)
B22	=LOOKUP(A22,$G$1:$H$5)
B23	=LOOKUP(A23,$G$1:$H$5)
B24	=LOOKUP(A24,$G$1:$H$5)
B25	=LOOKUP(A25,$G$1:$H$5)
B26	=LOOKUP(A26,$G$1:$H$5)
B27	=LOOKUP(A27,$G$1:$H$5)
B28	=LOOKUP(A28,$G$1:$H$5)
B29	=LOOKUP(A29,$G$1:$H$5)
B30	=LOOKUP(A30,$G$1:$H$5)

 

[hisyedaduil]

Thanks weaver. its working super fine.

May be i had messed up something previously.

 

[Weaver]

Not to worry. It's possible that the references had gotten jumbled.

I think it was worth persevering with because this format is easier to change and add to.

 

[hisyedaduil]

yes absolutely. its more easier more simpler. Very easy for even a starter to learn..Thanks again.