conditional formatting for custom format

Question

I am using excel 2013.
I want to create a rule for conditional formatting like as below:
The format is "hh:mm tt- hh:mm tt".

I values for this format is like as below-
1. 10:00 AM-11:13 AM
2. 11:49 AM-01:00 PM
3. 05:50 PM-08:44 PM etc.

I have tried like this
=AND(LEFT(A1, 2) > 0, LEFT(A1, 2) < 12, RIGHT(A1, 2) = "AM")
=AND(LEFT(A1, 2) > 0, LEFT(A1, 2) < 12, RIGHT(A1, 2) = "PM")
but did not get succeed.

Please provide a solution.

What is it stuck on? How are your values being entered? Are they being pulled from elsewhere or manually entered? The "AND" command is only going to get you a T/F statement, not really anything to work towards. — Joe Laviano
– Joe Laviano, Commented Sep 12, 2013 at 13:26
@JoeLaviano: The excel sheet is preparing by coding in c#, not entered manually. — Naman Goyal
– Naman Goyal, Commented Sep 13, 2013 at 4:25

Arjun Sharma · Accepted Answer · 2013-09-13 05:58:07Z

1

MID (cellno., startindex, length) can be used and matched for the followiling patterrn

10:00 AM-11:13 AM

You could try this

=AND(VALUE(LEFT(A1,2))>0,VALUE(LEFT(A1,2))<=12, MID(A1, 3, 1)=":", VALUE(MID(A1,4,2)) >= 0, VALUE(MID(A1,4,2)) < 60,OR(MID(A1,6,3)=" AM",MID(A1,6,3)=" PM"), MID(A1,9,1)="-", VALUE(MID(A1,10,2))>0,VALUE(MID(A1,10,2))<=12, MID(A1, 12, 1)=":", VALUE(MID(A1,13,2)) >= 0, VALUE(MID(A1,13,2)) < 60, OR(MID(A1,15,3)=" AM",MID(A1,15,3)=" PM"))

answered Sep 13, 2013 at 5:58

Arjun Sharma

5952 gold badges9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

conditional formatting for custom format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related