macro t compare column and return OK or CHECK

coolhit

Board Regular
Joined
Jul 16, 2009
Messages
90
Hi

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

Below is what Check Employee Hours looks like

21nprfk.jpg


Below is what Check Hours looks like

5xof15.jpg


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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jul 16, 2009
Messages
90
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What is the formula =((C4-B4)*60) supposed to represent? When formatted as [hh] it shows 240 but what is that?
 

coolhit

Board Regular
Joined
Jul 16, 2009
Messages
90

ADVERTISEMENT

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.
 

coolhit

Board Regular
Joined
Jul 16, 2009
Messages
90

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Why are you entering times with a decimal point? And how to you arrive at 30min and 10min for those times?
 

coolhit

Board Regular
Joined
Jul 16, 2009
Messages
90
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,311
Messages
5,836,583
Members
430,438
Latest member
David Gr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top