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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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","")
 
Upvote 0
=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]
 
Upvote 0
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","")
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top