Greg has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Date range function - please help
Hi Rodney,
I usually go through the online forum but my query requires me to attach the example to explain it. I hope you can help.
If you open the spreadsheet and refer to cell E23. The formula in this cell will add text and conditional formatting if the start date in cell C23 falls within the two dates in cells E18:E19.
This works fine for the start date but I now need to amend the formula so that it adds the text/conditional formatting to all the cells that are covered within the date range C23:D23. For example if the start date is 07/11/12 and end date is 20/12/12 then cells F23:L23 should appear in yellow (yellow text with yellow conditional formatting).
I can’t figure it out, please can you help?
Many thanks,
Greg
RE: Date range function - please help
Hi Greg,
Thanks for your question. Rodney is on leave, so I have posted this to the forum on your behalf. I have not attached the document, as it may be confidential.
I have assigned this question and sent the file to one of our other trainers, Doug. So he will take over the question from here. If he needs to send back an updated copy of the file, he will do so via email.
Any questions, please respond to this forum post, thanks.
Regards, Rich
RE: Date range function - please help
Thanks, I managed to figure it out last night in the end. I needed a few nested 'IF AND' formulas to get it to work.
Best,
Greg
RE: Date range function - please help
Hi Greg
I'm one of the MS trainers replying on Rodney's behalf.
To format the range E23:X77 to produce the yellow bars only for the Start/End date range try the following:
Highlight the range E23:X77
Select Condiional Formatting, New Rule
Use a formula to determine which cells to format
Enter this formula
=AND(E$18>=$C23,E$18<=$D23)
Click Format for yellow fill and yellow font colour.
It works on your sheet ok.
It's not clear to me what the other rule is for
Cell value = "=active"
The Weekly Planner sheet looks great!
Please let me know if the formula works, thanks.
Regards
Doug
Best STL
