Filling in timeline from two workbooks

Question

I have created a timeline in a general worksheet of Excel as follows:

Now the remaining thing for me to do is to fill it with content. For this I have a different file. See the image below:

I would like to find a way to fill in (with conditional formatting) the timeline from figure 1. How could I do this? There are different sections to the time line. I would prefer to do this without a VBA Macro but simple with equations.

Thanks in advance.

Answer 1

It was not clear in your "timeline" screenshot whether the time's also include date information, so I made the assumption they don't. In which case you'll have to add a helper column to your "different file" where I'll assume "Starting Time" is in A1:

=ROUND(A2-INT(A2),6)

I put this formula in column D for the following example:

Select C1 > Conditional Formatting > New Rule... > Use a formula to determine which cells to format > [this formula]:

=NOT(ISERROR(MATCH(ROUND(C$1,6),$B$2:$B$5,0)))

OK, now change the "Applies to" for that condition, to

=$C$1:$S$10

The ROUND is necessary because when the timestamps are compared, they cannot MATCH because they're fractions of days.

Obviously this won't match your data perfectly because not enough information was supplied in the question.