Clever conditional formating formula needed to look at time of day and date

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,931
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi everyone
I need a formula i can out into conditional formatting that can do this
return a true if
the date in a cell in column O is dated the 25th of the month in cell A1 before 12 noon?

ok so column O holds dates and times formatted like this "DD/mm/yy hh:mm:ss

i want the conditional formatting to show me the dates that are on the 25th before 12noon

please help if you can
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,853
Office Version
  1. 365
Platform
  1. Windows
Is this what you are after?

21 05 06.xlsm
AO
1April19/04/21 09:51:08
230/03/21 15:04:39
325/04/21 10:44:16
407/04/21 09:18:42
525/04/21 10:10:25
614/03/21 17:02:20
725/05/21 03:46:28
825/04/21 19:53:02
917/04/21 07:03:09
1025/04/21 15:11:45
1111/04/21 02:38:26
1225/04/21 11:59:59
1310/04/21 11:30:49
CF TWH
Cells with Conditional Formatting
CellConditionCell FormatStop If True
O1:O35Expression=AND(TEXT(O1,"ddmmmm")=25&A$1,MOD(O1,1)<0.5)textNO
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Excel Formula:
=AND(DAY(O1)=25,MOD(O1,1)<0.5)
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

OP to confirm of course but I think that you may have missed this part of the requirement
you are probably right.
But since I found the post slightly confusing i decided to go with the last part only:
i want the conditional formatting to show me the dates that are on the 25th before 12noon
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,931
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
my apologies for making this more confusing than i should ,
Thank you all for your help it is now working as I wanted
Thanks
Tony
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,853
Office Version
  1. 365
Platform
  1. Windows
Can you clarify for our benefit whether it needed to match a particular month or not?
 

Forum statistics

Threads
1,148,277
Messages
5,745,818
Members
423,980
Latest member
zimza

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