Fill dates between 2 dates

[Winda Tobias]

Hello!

I have a start and end dates but is there any way I can list the dates between those 2 dates.

so for example, if the start date is 1/1/20 and the end date is 1/4/20, is there any formula that will give me 1/1/20, 1/2/20, 1/3/20 and 1/4/20

what I am trying to achieve can be illustrated as below:

current data:

 

 

 

ID

Begin Date

End Date

Type

 

 

A1

8/5/2020

8/7/2020

x1

 

 

 

Desired result:

 

 

 

ID

Begin Date

Type

 

 

A1

8/5/2020

x1

 

 

A1

8/6/2020

x1

 

 

A1

8/7/2020

x1

 

 

 

 

Thank you!

[Fabian Duerr]

You could create a data function that does this job for you. You basically need to loop through every line of your data frame, read start and end date, create a subset with the new data rows (from start to end date), and at the end bind all of the subsets together. Let me know if you need further support here. It's an interesting task and similiar questions have been asked a couple of times already. If I find some more time later or tomorrow I can try to write a function for that and share it with you.

[Winda Tobias]

I ended up calculating the total dates in between with DateDiff formula and use that to calculate new column such as below:

case when [totaldatesinbetween]>=1 then DateAdd("day",1,[beginDate]) end

do that for about 20 times more than their max total dates in between to make sure I cover all of the dates and then unpivot them.

not the most advanced solution but I couldn't find any other way as of yet.

Thanks!

[Fabian Duerr]

As this popped up again I want to add the data function solution. For this solution it will be important that your data columns have the right format so that the as.Date operation works in R.

So starting from something like this...

 

Yo can get to something like that...

 

... by doing the following:

# dummy data set

df