time calculation results with multiple if

[apgmin]

In cell A1 I insert the date
In cell A2 I have the command =CHOOSE(WEEKDAY(A1),"SUN","MON","TUE","WED","THU","FRI","SAT")

In cell A3 I insert the worker IN time and in cell A2 the out time ( in mm:ss AM/PM format )

In Cell A4 I need the following results, can anyone please help me with the formula for the following conditions

If in A3 the time is above 9:00 AM then "late" ( As any worker coming after 9:00 AM is marked late )
If in A3 or A4 the time is above 14:00 PM then "halfday" ( As the worker can either come at 14:00 or leave after 14:00 to avail a hald day off )
If in A3 the time is "0:00" then "abscent"
If in A3 the time is on or before 9:00 AM and If in A4 the time is above 5:30 PM then "Present" ( As the worker is supposed to come before 9:00 AM and leave after 5:30 PM )
If A2 is "SUN" then "Present" ( then I will not feed A3 or A4 ) ( As for all worker sunday is day off )

Please help and Thanks in advance

 

[Saurabhj]

I think the second condition should be modified as this is contradicting first condition.

If in A3 or A4 the time is above 14:00 PM then "halfday" should be

1. Worker can come at 9:00 am and leave at 14:00 pm
2. Worker can come at 14:00 pm and leave at 17:30 pm.

 

[apgmin]

I think the second condition should be modified as this is contradicting first condition.

If in A3 or A4 the time is above 14:00 PM then "halfday" should be

1. Worker can come at 9:00 am and leave at 14:00 pm
2. Worker can come at 14:00 pm and leave at 17:30 pm.

correct

 

[Saurabhj]

Hi,

Please check below:

Book1
	A	B
1	17-Dec-21	Late
2	FRI
3	9:10 AM
4	6:30 PM
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IF($A$2="SUN","Day off",IF(A3=TIME(0,0,0),"Absent",IF($A$3>TIME(9,0,0),"Late",IF(AND($A$3<=TIME(9,0,0),$A$4>=TIME(14,0,0)),"Half Day",IF(AND($A$3<=TIME(9,0,0),$A$4>=TIME(17,30,0)),"Present")))))
A2	A2	=CHOOSE(WEEKDAY(A1),"SUN","MON","TUE","WED","THU","FRI","SAT")

 

[apgmin]

Correct Response :
If day is Sunday is shows day off
If A3 is 9:01 am it shows late
If A3 is 9:00 am or before and A4 is 2:00 pm or after it shows half day
Wrong Response :
If A3 is 9:00 am or before and A4 is 5:30 pm or after it shows half day instead of present
If A3 is 2:00 pm or after and A4 is 5:30 pm or after it shows late instead of halfday

 

[apgmin]

=IF($A$2="SUN","P",IF(A3=TIME(0,0,0),"A",IF(AND($A$3<=TIME(9,0,0),$A$4>=TIME(17,30,0)),"P",IF(AND($A$3<=TIME(9,0,0),$A$4>=TIME(14,0,0)),"H1",IF(AND($A$3>=TIME(14,0,0),$A$4>=TIME(17,30,0)),"H2","L")))))

THIS WORKS