Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Formula - timline with additional dates

Copper Contributor

Hi, 

I have a set-up calendar which highlights the cells based on the value in column B (start date) and column C (end-date). Like timeline. This is the formula I'm using

 

=IF(AND(L$5>=$E29,L$5<=$F29),"X","")

 

Now I am also required to highlight a single date from columns D,E and F. 

 

How I can do that?

 

Thank you

6 Replies

@Kinaia 

Which date do you want to highlight?

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar Thank you for your reply. 

 

I would like in cells L till JK (calendar) to show the timeline based on the column Active Start Date and Avtive End date but also highlight in that calendar dates from columns D,E and F. 

 

https://docs.google.com/spreadsheets/d/16QusoS_cDPN2DGwlG-9j-rCyvZW2eW6M/edit?usp=sharing&ouid=11173...

 

I hope this makes sense 

 

Thank you

 

@Kinaia 

I assume that you mean columns H, I and J instead of D, E and F.

And that the start date in E9 should be in 2023.

See the attached workbook.

This is exactly what I'm after.

Thank you!

Please how I can replicate this in the original tracker? (unfortunately I cannot see any formula)
best response confirmed by Kinaia (Copper Contributor)
Solution

@Kinaia 

I didn't enter any formulas in the cells.

Select cell L9, then on the Home tab of the ribbon, click Conditional Formatting > Manage Rules...

You'll see the 4 rules that I created. They're all of type 'Use a formula to determine which cells to format'.

Thank thank so much.
I really don't know why I didn't think of conditional formatting - again thank you so much!
1 best response

Accepted Solutions
best response confirmed by Kinaia (Copper Contributor)
Solution

@Kinaia 

I didn't enter any formulas in the cells.

Select cell L9, then on the Home tab of the ribbon, click Conditional Formatting > Manage Rules...

You'll see the 4 rules that I created. They're all of type 'Use a formula to determine which cells to format'.

View solution in original post