Finding an Error in Formatting Based on Time in Excel

Question

I have a column of times which span some twenty-four-hour-long interval (not necessarily starting at midnight or noon). The first one is filled in manually and then each subsequent one is autofilled according to the formula "[prevcell]+TIME(0,30,0)", where the previous cell is the cell immediately above the current one. The first cell is always going to be on the hour mark, so it will be of form "n:00:00" for some integer n. Thus, all subsequent times will either be on the new hour mark or on the thirty-minute mark, meaning that they will be of form "n:m0:00" where n is an integer and m is either 0 xor 3.

Then I tried to use conditional format the cells so that any cell which is on a thirty-minute mark (id est: is of the form "n:30:00" for integer n) has a light grey background color (whereas all other cells are white/no-color). So, for example, a cell with 10:00:00 in it will be white, but a cell with 10:30:00 would be light grey.

I did this:

1. Rule type: "Format only cells which contain".

2. Format cells with: "cell value", "equal to", "=TIME(HOUR([curcell]), 30, 0)". (Where the current cell is the particular cell being formatted itself.)

3. Selected appropriate background color formatting.

The formatting worked for most cells. I would perhaps understand if it did not work for the first or last cells for some reason, but it does usually. For what it does not work are exactly the cells with the following values: 11:30:00, 18:30:00 (6:30:00 p.m.), 21:30:00 (9:30:00 p.m.), 22:30:00 (10:30:00 p.m.); these cells are not colored (white) when they should be colored. It works for all other cells, including 23:30:00 (11:30:00 p.m.), even though it did not work for the cell for 11:30:00 (a.m.). There are no cells which are colored when they should not be; the only cells which are not colored when they should be are the aforementioned ones.

When I change the seed value/time in my topmost cell, different errors of the exact same sort arise (often but not always for the same values and sometimes for additional ones).

Does anyone know what is wrong or how to fix this?

---

Edit! Here are some observations which I have had:

• It does not work for any time after the next midnight, no matter what the time in my first cell is (except 00:00:00). The dependence on the first cell's value seems to go like this:
• 11:30 (a.m.) works for certain times in my first cell.
  1. It does not work for 00:00:00, 00:30:00, 1:00:00, 1:30:00, and 2:00:00.
  2. It works for 2:30, 3:00, 3:30.
  3. It does not work for 4:00, 4:30, 5:00, 5:30, 6:00, 6:30.
  4. It works for all times after 7:00, incrementing by 30 minutes, until and including 11:30. (Consideration no longer applies after that seed value).
• I did not check extensively, but from a quick glance: 18:30:00, 21:30:00, and 22:30:00 never work.
• Depending on the value of my first cell, certain other n:30:00 times do not work (for integer n), but I cannot discover a pattern. There are no errors, as far as I can likewise tell, for any n:00:00 times (for integer n).

---

Update #1

I have changed the formatting condition to: Format cells with "cell value", "not equal to", "=TIME(HOUR([curcell]), 0, 0)". (Where the current cell is the particular cell being formatted itself.)

This seems to have improved the situation some, but not perfectly. It formats everything correctly except for the fact that it improperly colors cells with these time: 20:30:00 (8:00:00 p.m.), 23:00:00 (11:30 p.m.), and (if present) all times after and including the next midnight (24:00:00; a.k.a.: the next 12:00 a.m.).

(Edit: The times for which it improperly colors seem to depend on the value of my first cell and perhaps other factors. But there are always two discrete errors later in the day and the after-midnight problem.)

---

Update #2

The main problem has been solved. However, if anyone can tell me why it arose in the first place, I would be very thankful. Curious minds want to know!

Answer 1

Check the Screen Shot below, shows successful Colour Format the MINUTE part in Grey Colour in the Cells, if are 30, as you have demanded.

Follow these steps.

1. Select the Data Range where you are suppose to enter the TIME.
2. Apply Cell Format as HH:MM:SS, then enter the TIME values.
3. Select the DATA RANGE to apply Conditional Format.
4. In Home TAB click Conditional Formatting,New Rules and finally Use Formula to determine which cell to format.
5. Write this Formula =MINUTE(A2)=30then select the Colour finish with OK, where A2 is part of the Data Range you have currently selected.

NB: Remember for better understanding only in Column B I've mentioned TIME PERIODs since you have written also the Conditional Formatting is not working after MID NIGHT and other.

Also, in Column C, I found MINUTES from the Dates in Col A just to show and match the values in adjacent cell to make sure that my Solution is only Formatting Cell in Grey Colour if the Minute value is 30 only.

Hope the work for you.

---

NB: Alternatively, follow above written steps and in place of the =MINUTE(A2)=30 formula you can use this one too,=TIME(0,MINUTE(A2),0)=TIME(0,30,0).

Answer 2

I assume this problem is caused by the way excel calculates, see this link

https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

So when you add times the result is not exactly on the minute but slightly over or under, so you don't get an exact match with your comparison

Using MINUTE function will work when the value is slightly over, but not under. You can check my theory by using SECOND function on your data to see if you have any values of 59

For a fix, given you are dealing with whole minutes only you could round to the nearest minute then check the minute value, e.g. for a time value in A2

=MINUTE(MROUND(A2,"0:01"))=30

Update:

I checked this by putting time values in one column and then used your formula in the next column, so where my time values started at A2 I used this formula in B2 copied down

=A2=TIME(HOUR(A2),30,0)

For 18:30 I get FALSE and when I check the values the value in A2 is this

0.770833333333334

but the value from TIME(HOUR(A2),30,0) is this:

0.770833333333333

So no match unless you round the data before comparison as suggested