Date range function - please help

Resolved · Medium Priority · Version 2010

Wed 16 Jan 2013, 12:08 Delegate Greg said...

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

Wed 16 Jan 2013, 12:13 Trainer Rich said...

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

Wed 16 Jan 2013, 12:28 Delegate Greg said...

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

Wed 16 Jan 2013, 15:16 Trainer Doug said...

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

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Calculate difference between two times

For presenting the result in the standard time format (hours : minutes : seconds . Use the subtraction operator (-) to find the difference between times, and the TEXT function to format the returned value to text in a specific number format.

Hours never exceed 24, minutes never exceed 60, and seconds never exceed 60.

=TEXT(B2-A2,"h")
Hours between two times (4)

=TEXT(B2-A2,"h:mm")
Hours and minutes between two times (4:55)

=TEXT(B2-A2,"h:mm:ss")
Hours and seconds between two times (4:55:00)

Where B2 and A2 must hold the end time and start time respectively formatted as a time format

View all Excel hints and tips