Using date picker to fill a different cell with a color

[mwcvmaker]

Hello all,

I hope this isn't a repeat question (I searched beforehand) but I would like to create a planning chart where I have a start date column and end date column and have those dates and everything in-between filled in with a color on a timeline. This would speed things up and eliminate manually filling date cells that are very small.

many thanks!

 

[Joe4]

Welcome to the Board!

I think you may be able to use Conditional Formatting to do this.
See this, especially the second part: How to highlight cells between two dates in Excel?

If you are having trouble implementing it, please provide the details (i.e. specific addresses where these dates occur on your sheet), and show us an image of what your sheet looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[mwcvmaker]

Welcome to the Board!

I think you may be able to use Conditional Formatting to do this.
See this, especially the second part: How to highlight cells between two dates in Excel?

If you are having trouble implementing it, please provide the details (i.e. specific addresses where these dates occur on your sheet), and show us an image of what your sheet looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi @Joe4 ,

Thank you for the warm welcome and the support. I have uploaded an image for reference. I would like to select a start date and end date (column C and D) and have a color shown between those dates. I am not sure the above solution quite hits the mark. It could also be that I am not implementing it 100% correctly.

Thank you again for the help

MW

 

[Joe4]

In looking at your data, I think the big problem may be that you aren't dealing with real dates. This will be made much easier if you have real dates in there.
The good news is it is fairly easy to change your data to use real dates without changing how it looks.

So, it looks like cell S4 may have the first day of the year, and it is showing "01" in that cell.
Instead, enter the actual date in cell S4, in a valid date format (so it is entered as a date and not text).
In the US, that would look like: 1/1/2023 (if in any other country, enter what date format it requires).

And then you can extend that out quickly by placing this formula in cell T4:

=S4+1

and copy that across row 4 as far out as you need.

Now, simply apply a custom format to row 4 of 00, and it will display those dates exactly as you show in your picture.
But more importantly, now row 4 is a row with valid dates that you can use the date functionality on, and should be able to use Conditional Formatting to do what you need.