Multiple Criteria Help

ozstephan

Board Regular
Joined
Nov 19, 2005
Messages
52
I have a spreadsheet which is used to sign in and out of an office. I would like to be able to put a summary at the top of the page. So I am chasing a formula's. I'll explain:

Column A=date
Column B=Staff Name
Column D=Time Returning
Column E=Yes/No (this is to indicate a vehicle has been taken)

Formula I need help with. all criteria will be on the same row.
1. If column A = today & column B = Brian & column D = less than current time then cell with formula says Yes.
2. If column A = today & column B = Brian & column D = less than current time & colun E = yes then cell with formula says Yes.

These would be two different cells with the two different formula.

If anyone can help it would be appreciated.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Something along these lines...

Formula 1:

Code:
=IF(AND(A2=TODAY(),B2="Brian",D2<NOW()),"Yes","")

Formula 2:
Code:
=IF(AND(A2=TODAY(),B2="Brian",D2<NOW(),E2="Yes"),"Yes","")
 

ozstephan

Board Regular
Joined
Nov 19, 2005
Messages
52
=IF(AND(A2=TODAY(),B2="Brian",D2<NOW()),"Yes","")

Hi, thaks for that. It's mostly working, except that I am having trouble getting it to recognise the time. I have tried times on either side of now and it seems to say yes no matter what time it is. Any suggestions? Thanks[/quote]
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
I am having trouble getting it to recognise the time

My fault. The formula =NOW() returns today's date and time. If the cell in question will only contain a time but no date, we need to use replace NOW with TIME. This SHOULD work

Code:
=IF(AND(A2=TODAY(),B2="Brian",D2<TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))),"Yes","")
 

Forum statistics

Threads
1,141,095
Messages
5,704,311
Members
421,338
Latest member
Pepess

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