Excel [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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)
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!