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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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