1

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!

  • Let me ask one simple question, whether you want all the cells in Grey Colour if the Minute part of the TIME is ONLY 30 ? – Rajesh S Jan 30 '18 at 6:13
  • Well, if it is not "00", then it will be "30" according to the way that the sheet is set up to work. So, if you have a solution that colors anything that is not "00" in the minutes part (and leaves minute-"00" alone), then that will work. – user173897 Jan 30 '18 at 6:20
  • Note that the formula "cell value is not equal to =TIME(HOUR([curcell]), 0, 0)" works better, but improperly colors 20:00:00, 23:00, and everything after (and including) 24:00:00 (the next midnight) if those values are present. (So, I changed the formula to this, because it is better. But the basic problem nonetheless remains) – user173897 Jan 30 '18 at 6:25
  • ^^ Actually, the aforementioned times for which it does not work are based on the time in my first cell (and maybe other factors). But there are always two discrete errors and then the after-midnight problem (maybe it turns into a pumpkin?). – user173897 Jan 30 '18 at 6:39
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.

enter image description here

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).

  • I have never gotten "Use formula to determine which cells to format" to work for me. Including this time. :( – user173897 Jan 30 '18 at 6:58
  • Go to Conditional Formatting, then find New Rules click it, a window will appear, find Select Rule Type, find last one, Use a Formula to determine which cells to Format, hit it, find Edit the Rule Description, then in the Text box enter the Formula I've suggested. – Rajesh S Jan 30 '18 at 7:11
  • Hope you found it. – Rajesh S Jan 30 '18 at 7:11
  • I did that, but it did not work. However, your idea did (after some work) give me one of my own, which did work: Do as you suggested, but the formula is: =Time(0,Minute([curcell],0)=Time(0,30,0). So, I will accept your answer. :) Thank you so much! – user173897 Jan 30 '18 at 7:19
  • 1
    Nice to hear from you, it's okay that you have applied the Formula of your choice,, look for a problem there may be many kind of possible solutions. I've used the Minute formula and data shown by you in OP and uploaded the Screen Shot,, anyway keep asking. – Rajesh S Jan 30 '18 at 7:28
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

  • Nice work! Thanks! That was driving me nuts! – user173897 Jan 30 '18 at 20:50

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.