Excel

Closed
maggi - Feb 5, 2010 at 01:59 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 5, 2010 at 06:32 AM
Hello,

I have cells with the time of the emp who have punched the time they have come to the office. I need to find the late comers.

The values in the cells are in general format. should i change to time format.

eg.8:00 AM
8.15 AM
10.15 AM
7.00 AM
12.45 AM
3.00 PM
8.00 AM

using if function if a person comes after 8.15 the value should be 1 and for others 0.

I used =IF(J17>"08:15",1,0) but i don't get the correct anwere.

2 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 5, 2010 at 04:49 AM
because of "am" part

Since you have both AM and PM time, For you the most easy way would be use 24 hours time

so write 8:15 AM as 815 and 8:15 pm as 2015

then you can simply use the compare as you wanted

=IF(J17>815,1,0)
0
Hi,

Thanku for the idea.

But this data is imported from the punching machine into excel format and i get the details in this format (8:15 AM ) by default so is there any other possible method. This cell is in general format. will it help if i change the format. pls help,because changing the details to the format u told be is going to be tedious.there are lot of employees.

Name Date Timetable Clock In Clock Out Late
aaaa 03-Jan-10 8am-6pm 8:08 AM 6:44 PM 0 0
aaaa 04-Jan-10 8am-6pm 8:44 AM 6:39 PM 1 1
aaaa 05-Jan-10 8am-6pm 8:37 AM 7:14 PM 1 1
aaaa 06-Jan-10 8am-6pm 8:09 AM 6:57 PM 0 0
aaaa 07-Jan-10 8am-6pm 8:57 AM 6:59 PM 1 1
aaaa 09-Jan-10 8am-6pm 7:29 AM 6:42 PM 0 0
aaaa 11-Jan-10 8am-6pm 8:38 AM 6:52 PM 1 1


Regards,
Margaret
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 5, 2010 at 06:32 AM
Maggi
Check one thing. In the clock in column, when you click on the cell d2 (based on your sample data)
do you see a the top value as "8:08 AM" or do you see it like 8:08:00 AM. Again not the the cell but the top in the formula bar

When I imported your example, though the cell seem to show the value as "8:08 AM" but actually if i look at the formula bar, it appears as 8:08:00 AM.
0