formaula to derive late comings to office

[vinod9111]

Hi,

I need a formula which can pop up late if the individual has log in to system beyond 9:40 am, but if the person has left office at 9:00 pm , then he will get grace of 1:30 hour next day i.e he can reach office by 11:00 am next day but if he comes beyond 11:00 then he is late.



Date Login Time Logout Time Output
01-Apr-19 9:52:36 18:48:20 Late
02-Apr-19 9:35:02 21:52:58
03-Apr-19 10:29:24 18:44:38
04-Apr-19 9:43:34 21:42:58 Late
05-Apr-19 11:13:50 18:31:29 Late

regards,

Vinod

 

[Gerald Higgins]

Let's make some assumptions.

When you say

...if the person has left office at 9:00 pm...

you actually mean

...if the person has left office AFTER 9:00 pm...

The grace period of 1 hr 30 mins is not compatible with a revised start time of 11:00, if the normal start time is 09:40, therefore I have assumed the revised start time is 11:10.

And your data is laid out such that the 9:52 Login Time on 01-Apr-19 is in cell B2.

In cell E3

=IF(B3>TIME(9,40,0),IF(C2>TIME(21,0,0),IF(B3< TIME(11,10,0),"","Late"),"Late"),"")

And copy down as far as required.
Note that this is in E3, NOT in E2, as there is no previous day to calculate the grace period for.
You can write a simpler version of this formula for E2 if you need to.

 

[vinod9111]

Thanks Gerald, it worked perfectly.

 

[Gerald Higgins]

Hth, tftf