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