Need your help with Timeline

[MOhammedaldb]

Hello guys how are you doing I hope you are doing well,

I need your guy's help adding a timeline column in excel, the problem that I have is that the calculation is not straight forward I will share with you a pic to get an idea of how the dataset is structured 

as you can see there are some deals with data in the "Close Date" column and some deals that are blank, this is because the deals with data in the "Close Date" are closed deals either "Won" or "Lost" but the deals with blank data in the "Closed Date" are open deals such as " Early Stage " and " Upside" so the deals are not closed yet, what I want to do is a timeline for each deal to see from the moment we took the deal " Created on " to the moment we closed the deal or in the case of open deals the date of today or if this is complicated we can take the " Modified on " which is the latest modification on the deal ( i prefer if we could take the date of today to give an accurate timeline but if this is not possible it's ok ) and here is a Google Drive link for a video to help you understand what I mean if it was not clear

https://drive.google.com/file/d/1k2RBSuPS8imY9TzArDZhKf2d_oqHOOti/view?usp=sharing

thank you so much in advance and please if you have any questions please let me know

System :- PC/Windows 10

Product name :- Microsoft 365 apps for enterprise 

version number :- 2302(Build 16130.20332 Click-to-Run)

File :- https://docs.google.com/spreadsheets/d/1rUQ4G-L8m2yJ7f7VenENgnbYiu4D0Oij/edit?usp=sharing&ouid=10930...

[#2]

@MOhammedaldb 

To create a timeline column in Excel that shows the duration of each deal from the "Created On" date to the "Closed Date" or "Modified On" date (for open deals), you can follow these steps:

1. Add a new column next to the "Closed Date" column and name it "Timeline" or any other name you prefer.
2. In the first cell of the "Timeline" column, enter the following formula:

=IF(ISBLANK(D2),TODAY()-C2,D2-C2)​

This formula uses the IF and ISBLANK functions to check if the "Closed Date" column is blank or not. If it's blank, it calculates the duration from the "Created On" date (in column C) to today's date using the TODAY function. If it's not blank, it calculates the duration from the "Created On" date to the "Closed Date" date (in column D).

• Copy the formula from the first cell to the rest of the cells in the "Timeline" column by dragging the fill handle (the small square at the bottom right corner of the cell) down to the last cell.
• Format the "Timeline" column as a number with two decimal places and choose a time unit that suits your needs (e.g., days, weeks, months, years).

This should give you a timeline column that shows the duration of each deal in the specified time unit. Note that the result may be negative if the "Closed Date" or "Modified On" date is earlier than the "Created On" date, which means that the deal was closed or modified before it was created.

[#3]

@MOhammedaldb 

The formula I used calculates the duration

duration
= IF(close>open, close - open, today - open)

This is then used as the positive error bar of a scatter chart in which the project number is plotted against the opening date.