Creating a consolidated column

[Shamash]

Hello folks,

I want to create a consolidated column by utilising a forecast and actual dates column for a specific task , if a task is actualised the consolidated column show the date from actualised column otherwise it should keep showing the dates from forecast column so far it sounds simple and can be achievable using if else statements though the complexity is added when I want to conditionally format the cell in the consolidated table as green if it is actualised , use color red when the forecast date is overdue and use color orange when the forecast date is upcoming in the next 7 days.Any guidance in how to achieve it in one consolidated column is much appreciated.

 

[maabadi]

Welcome to MrExcel Message Board.
Please upload Example Data with XL2BB Add-in (Preferable). Or Upload it to Free Hosting Site e.g. GoogleDrive, OneDrive, DropBox and ... then Insert Link here.

 

[maabadi]

How about:
At Conditional formatting menu Select New Rule, then Select Use a formula to Determine ...

Book1
	A	B	C	D	E
1	Tasks	Forecasing Date	Actual Date	Consolidate Date
2	TK1	7/22/2021	7/25/2021	7/25/2021
3	TK2	7/29/2021	7/28/2021	7/28/2021
4	TK3	8/5/2021	8/11/2021	8/11/2021
5	TK4	8/12/2021	8/22/2021	8/22/2021
6	TK5	8/19/2021	8/24/2021	8/24/2021
7	TK6	8/26/2021	9/1/2021	9/1/2021
8	TK7	9/2/2021		9/2/2021
9	TK8	9/6/2021		9/6/2021
10	TK9	9/9/2021		9/9/2021
11	TK10	9/11/2021		9/11/2021
12	TK11	9/14/2021		9/14/2021
13	TK12	9/17/2021		9/17/2021
14	TK13	9/20/2021		9/20/2021
15
Sheet1

Cell Formulas
Range	Formula
D2:D14	D2	=IF(C2="",B2,C2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D14	Expression	=AND($D2-TODAY()>0,$D2-TODAY()<7,C2="")	text	NO
D2:D14	Expression	=AND($D2<TODAY(),C2="")	text	NO
D2:D14	Expression	=$C2=$D2	text	NO

 

[Shamash]

Thanks, I was looking for some guidance in PBI to implement this scenario.