date timeline from start & end dates in excel

[Seko_sonogo]

 

In this sheet, I would like to have a date timeline generated from 6 inputs:

1- start date ( where timeline should starts )

2- end date ( where timeline should ends )

3- data presenting type (days, weeks or months)

4- weekend type to be excluded from timeline ( Friday & Saturday, Saturday & Sunday or No Days off)

5- weekend days that would be included in timeline ( due to overtime for example )

6- vacations should be excluded from timeline ( if same date is in vacation table & weekend days to include in timeline table, dates should be included in timeline.

I tried achieving this through "if" function, but I couldn't, it goes messy! I only succeed in generating days timeline as shown in the image! is it possible to achieve that in excel 2016?

[#2]

@Seko_sonogo 

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?

And what do you want to do with the data presenting type (days, weeks or months)?

[#3]

@HansVogelaar 

Dear Mr. Hans, thank you for your respond,

for the presenting data type, I meant that, if I choose "Days", time line would appear as days dates, if I choose "Weeks", timeline should appear as "week 1 of the month", Week 2of the month" and so on.

I attached an example file to demonstrate the problem through this link: 

https://drive.google.com/drive/folders/1GUut_8RTvaw0miPbSU-7oI_o1u8tz0sf?usp=sharing

[#4]

@Seko_sonogo 

How should we treat weekend dates, vacation dates and dates to include if the timeline should display weeks?

And how if the timeline should display months?

[#5]

Good question,
For example, we have weeks timeline,
Suppose week one (all the 7 days) are in vacation table, but we included Friday & Saturday dates to include in the other table, so now Week 1 has two working days which are Friday & Saturday, for that, it should appear in the timeline just as week 1. If all the week 1 days are off days, week 1 will not appear and week 2 will appear first instead of week 1. Same goes for months too.

[#6]

@Seko_sonogo 

I'll see if I can come up with something, but it's complicated.

[#7]

Sure!
Thank you so much for your reply & for you effort in advanced.

[#8]

I guess need to prepare a long list of whole 365 days of one year and then filter on this list.

[#9]

@Seko_sonogo 

The code in the attached workbook could be optimized, but please check whether it does what you want.

[#10]

It works perfectly!
Thank you so much sir,
You really helped me a lot, Im grateful and thankful for that!

May I use it for both my personal & my commercial uses ?

Thank you again!

[#11]

@Seko_sonogo 

Sure, go ahead.

If you use it commercially, it would be nice if you mention somewhere in the workbook that you got help from Microsoft Tech Community.

[#12]

Nice work I just downloaded it to excel on the web and I got a warning that that version doesn't interact with forms controls. Not sure if it'll be fully functioning on the web version. Just so people are aware.

[#13]

@Al_B_inPT 

The workbook contains a VBA macro. It will only work in the desktop versions of Excel for Windows and Mac, not in Excel Online (on the web), nor on Android and iOS.