Thanks for posting. If you're unable to get a helpful response sometime shortly I would suggest posting a packaged workbook with any work you've done so people can play around. My hunch says the heart of the issue is not having a set end date in the fields which are likely being read as nulls because of the blanks. That would likely have to change in excel. But i'm not totally sure.
I hope we're able to help!
PS: You can also contact firstname.lastname@example.org with your workbook and a description of what isn't working how you would want it to, and hopefully they can help.
I think method 1 (weekly, monthly) and 2 (daily) in The Cross Join Collection are tailor-made for what you want.
You might also be inspired by similar questions and their answers in FAQ: Open & Close Dates.
Ps. The new Excel connector in Tableau 8.2 (and 8.3) doesn't support custom SQL. The good thing is that custom SQL is possible if you open Excel with the Legacy Connector: Re: Where did the Custom SQL go in 8.2?