macro t compare column and return OK or CHECK

[coolhit]

Hi

I have 2 sheet
1-"Check Employee Hours"
2-"Check Hours"

Below is what Check Employee Hours looks like

Below is what Check Hours looks like

I want to the user to press ebutton i.e Sunday and macro does the comprsion.

Process

I would like macro to look at sheet "Check Hours" column "E" (cell E4) and compare it against the sheet "Check Employee Hours" column "E" (cell E7). If the hours matches i would like to say OK on sheet "Check Hours" cell E4, if it doesn't match then say CHECK.

Please can someone help as i have been trying for over a week now and not getting anywhere.

Any assistant would be appricated.

Regards

 

[Andrew Poulsom]

Can't you just use a VLOOKUP formula in E4 on sheet Check Hours?

=IF(VLOOKUP(A4,'Check Employee Hours'!B:E,4,FALSE)=((C4-B4)*60),"OK","CHECK")

 

[coolhit]

Can't you just use a VLOOKUP formula in E4 on sheet Check Hours?

=IF(VLOOKUP(A4,'Check Employee Hours'!B:E,4,FALSE)=((C4-B4)*60),"OK","CHECK")

Hi Andrew

I have just tried your formula it not working, on "Check Employee Hours" column 4 has a formula the following formula

=SUMIF('Daily Data Input'!C$6:C$3015,B31,'Daily Data Input'!F$6:F$3015)+SUMIF('Daily Data Input'!C$6:C$3015,B31,'Daily Data Input'!G$6:G$3015)+SUMIF('Daily Data Input'!C$6:C$3015,B31,'Daily Data Input'!H$6:H$3015)+SUMIF('Daily Data Input'!C$6:C$3015,B31,'Daily Data Input'!I$6:I$3015)+SUMIF('Daily Data Input'!C$6:C$3015,B31,'Daily Data Input'!J$6:J$3015)+SUMIF('Daily Data Input'!C$6:C$3015,B31,'Daily Data Input'!L$6:L$3015).

Will your formula work against another formula.

Regards

 

[Andrew Poulsom]

What is the formula =((C4-B4)*60) supposed to represent? When formatted as [hh] it shows 240 but what is that?

 

[coolhit]

What is the formula =((C4-B4)*60) supposed to represent? When formatted as [hh] it shows 240 but what is that?

B4 is Shift Start time
C4 is Shift End time
*60 is converting into minutes.

 

[Andrew Poulsom]

To convert to minutes you need:

=((C4-B4)*24*60)

formatted as General.

 

[coolhit]

To convert to minutes you need:

=((C4-B4)*24*60)

formatted as General.

Hi Andrew

Thank you very much for helping me out.

I have a small question in your formula below

IF(VLOOKUP(A4,'Check Employee Hours'!B:E,4,FALSE)=((C4-B4)*24*60),"OK","CHECK")

I would like to subtract break time. cell is format in custom [H].

I key in 1.25 to get 30min
I key n 0.45 to get 10min

This will be my last question on this.

 

[Andrew Poulsom]

Why are you entering times with a decimal point? And how to you arrive at 30min and 10min for those times?

 

[coolhit]

Why are you entering times with a decimal point? And how to you arrive at 30min and 10min for those times?

Andrew

I am entering time in decimal point to get 30min and 10 min.

Break time is all depandent on shift pattern (i.e if staff work 8hrs shift they get 30min and if they work less then 8hrs they get 10min)

Regards
Hitesh

 

[Andrew Poulsom]

Sorry I don't understand. Times should be entered with a colon.