conditional formatting of cell = multiple of ref date recurring ( Days or Weeks or Months)

EDATE simply adds the number of MONTHS so for 8 (b-annual) it adds 24 while the other calculations simply add number of days so 28 for a 4-week (not calendar) month.

Not sure what you mean by "reverse" this.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thank you.

Thank you JohnTopley

I haven't been able to spend the time I wanted on a moc up. But in a nutshell what I'm after is:

If a cells date= specified multiple (day of the week, number of days, months or years) selected in another cell, then highlight a third cell, else blank.

I hope this is much clearer. I'm sorry I'm so new to excel I'm struggling even to explain what I'm trying to achieve.

Thanks again

Sorry but need a file: just 5 or 10 rows with expected output will suffice.

Hi again JohTopley,

You actually solved my question Thankyou,

I've uploaded my moc up. Please let me know if you can see anything that's going to lead to an error. My aim is to nest all of the conditional formatting formula so whatever combination of days and frequency of recurrence is selected the dates in the timeline will be formatted to illustrate each recurring task accordingly.


I haven't got it exactly how I want it but in the process of mocking it up, with what you've said in mind I've managed to workout all the workings I need on the "Trials" tab to get the conditional formatting to work (It's currently just set to "yes" / "No"). The "Shapes" tab is the one I'll end up with the finished project on. It'll have options to select each of the frequencies on the Frequencies Tab.

I plan on creating data entry user forms for task/note entry.

My only observation is on the "Shapes" tab: why have you merged cells rather than just make the (a single) cell width narrower? If you are to do any calculations based on that sheet it will be a nightmare!

But glad you it working though not sure (how) I helped!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi JohnTopley,

Each of the cells in the hidden row 1 in Shapes have 1/4hr time intervals so task durations can be shown to 1/4 hr accuracy.

Once I understood that:
"EDATE simply adds the number of MONTHS so for 8 (b-annual) it adds 24 while the other calculations simply add number of days so 28 for a 4-week (not calendar) month."

I was able to work it backwards so if the frequency for recurrence reference is 8 and the date/time in row 1 minus the start date is able to be divided by the interval EDATE with zero remainder using MOD then format the cell in the same row as the task in that column. very happy chappy thank you. Are you happy for me to keep the thread open until I'm sure I've actually been able to apply the moced up Trails to the actual Shape Tab in case I need your help?

Yes: i "discovered" the 1/4 hour intervals BUT I would still advise against using merged cells: they cause many problems if they are required in calculations.

It would be better if you closed this thread (mark as SOLVED) and opened a new one if and when required: you can always refer back to this thread if appropriate.

Okay, thanks for your help JohnTopley