Skip to Content
0
Jun 19, 2020 at 12:38 PM

Fill in all dates between two dates

181 Views

Hi together,

i have a problem and i can´t find a proper solution. I have an Excel File with start and end dates and a serial number, which i uploaded as a query.

It's a list of devices and the beginning and end of their warranty, it looks like that:

Serial no. warranty start warranty end

0001 01.01.2020 01.01.2022

0002 03.01.2020 03.01.2022

0003 03.01.2020 03.01.2022

and so on..

i have uploaded another excel file just with dates in it from 01.01.2018... 01.01.2030 which i tried to use as a calender.

What i need is the amount of serial numbers which are in warranty for every day.

I want to know how many serial numbers are in warranty for each day from report begin till the end like this:

01.01.2020 1

02.01.2020 2

03.01.2020 3

and so on..

now what i found is the function TimeDime() but that doesn't really helped me. My approach would be to create a table with all dates between warranty start and warranty end for each device like that:

0001 01.01.2020

0001 02.01.2020

0001 03.01.2020 ....

Then just do a count of serial number..

best regards