Conditional Formating in Excel comparing dates between cells

0 votes

To compare the Day and Time in the cells of my two columns with one another, I use conditional formatting.

M contrasts with K, and N contrasts with L.

If the cell data is = in either situation, it should return Green. Red should be returned if >.

I've completed 45 additional data entry tasks, and up until the final row, every day and hour was flawless. Only those cells are visible on Friday. Any theories as to why this is occurring?

The row directly above it in the photographs serves as an illustration of how well the formatting is working.

Additionally, the cell formatting is dddd hh:mm.

enter image description here enter image description here enter image description here

I did a quick validation formula to see if N46 > L46, produce Y and it does flag as being greater for some reason. enter image description here

5 days ago in Others by Kithuzzz
• 27,740 points
41 views

1 answer to this question.

0 votes

To get a date from your strings, you'll need helper columns.

You can utilize this formula, and perhaps it makes sense to develop a LAMBDA function from it (search for tutorials that address this issue). A3 represents the cell containing your data (e.g. Friday 11:30)

=LET(d,A3,
day,TEXTBEFORE(d," "),time,TEXTAFTER(d," "),
weekdays,{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"},
weekdayIndex, MATCH(day,weekdays,0), (TODAY() + (weekdayIndex-WEEKDAY(TODAY(),2) +1) ) + time)

Then you can use the helper columns for your conditional formatting. I hope this helps you.

answered 5 days ago by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Split text cells in 70 character chunks in Excel

You can do this using Power Query Data ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 51,240 points
115 views
0 votes
1 answer

How to merge two cells in excel with same field name

Insert 2 new columns, G & H. Enter ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 51,240 points
148 views
0 votes
1 answer

In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 51,240 points
145 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
470 views
0 votes
1 answer

To simulate construct of other language does Python have a ternary conditional operator?

You can index into a tuple: (falseValue, trueValue)[test] test needs ...READ MORE

answered Oct 15, 2018 in Python by findingbugs
• 4,780 points
239 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,484 views
0 votes
1 answer

Conditions in Excel: skip cells with dates without a year

Both Google Sheets and Excel save dates ...READ MORE

answered Jan 17 in Others by narikkadan
• 51,240 points
36 views
0 votes
1 answer

What are the RGB and HEX codes of the 3 color conditional format in Excel?

In Excel 2016 at least the colors ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 51,240 points
1,046 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP