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

Jan 26, 2023 in Others by Kithuzzz
• 38,000 points
516 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 Jan 26, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Why does difference between same dates shows a difference of 1 month in Excel?

That's because you formatted the output using ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,000 points
555 views
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
• 63,600 points
839 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
• 63,600 points
1,625 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
1,254 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,020 points
1,113 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
669 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,020 points
4,391 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, 2023 in Others by narikkadan
• 63,600 points
422 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
• 63,600 points
8,732 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