IF Formula -How to add another IF Stament

[HznWFM]

Hi,

A little help for my formula below please? I wanted to show in column E that if the actual login is equal to the person's schedule, she will be tagged as "P" or present and if he's tardy from his schedule but didnt exceed more than 5mins late, then he will still be tagged as Present. I wanted to add this statement to my current IF formula which is: If(E2<=5,"P") but i am getting an error "You've entered too many arguments for this function. Can somebody help please? THanks a lot!!

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Schedule[/TD]
[TD]Actual Login[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tanya[/TD]
[TD]7:00 AM[/TD]
[TD]7:05 AM[/TD]
[TD]=IF(d2<=c2,"P",ABS(d2-c2)*1440)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Rap[/TD]
[TD]VL[/TD]
[TD]#NA[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

Hi, welcome to the board

I think the message you are seeing is more like you are creating a circular error?

You cannot (normally) create a formula in a cell that references that same cell, it creates a circular reference error.

Also, What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Sat 12 Aug 2017) is actually 42959

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So <=5 will always show as TRUE. You need to adjust that to something like 5/60/24 (or 0.003472222)
If you need to test for >= 7:00 AND <=7:05, you could use something like this...
=IF(AND(c2>=TIME(7,0,0),d2<=TIME(7,5,0)),"Y","N")

 

[Fluff]

You could also try expanding your existing formula to
=IF(D2<=C2,"P",IF(ABS(D2-C2)*1440<=5,"P",ABS(D2-C2)*1440))

 

[HznWFM]

HI,

Thank you. This one worked!

 

[FDibbins]

HI,

Thank you. This one worked!

which 1?

 

[HznWFM]

@FDibbins: Thanks for the time and for sharing some of your inputs! Appreciate it!

 

[HznWFM]

this one from Fluff:

=IF(D2<=C2,"P",IF(ABS(D2-C2)*1440<=5,"P",ABS(D2-C2)*1440))

 

[Fluff]

Glad to help & thanks for the feedback