Forum Discussion
Helper IIConvert date ranges into list of dates?
In M it can be done in various ways but the easiest would be to:
- convert start and end dates to numbers,
- create lists from those numbers,
- expand the lists and
- convert the numbers back to dates
as demonstrated in this short video.
I have a date range which goes in minutes at an increment of 15 minutes. When i use this method to convert in whole numbers. It takes away the minutes and reset every date to 12:00 AM. I have tried to use decimal instead of whole numbers but still facing error.
Could you please provide the solution if the dates have minutes.
Helper IIHi yashasvi
You could split the process in two. Convert your dates into a list first using {[Date From]..[Date To]}.
Then once you have that list, do another list {[0]..[96]} this represents the minutes i.e. there are 96 * 15 mintues = 24 hours. Then multiply the minutes 0.0104166666666667, this is the result of =15/(24*60) which will get you the day and minute value (at 15 minute) intervals.
Add the date and minutes column together and covert to date & time
Hope that helps
Thanks for the quick response Dan80 . When I tried to use your M query to handle Dates which has minutes. It is showing error. Perhaps because when the dates are in minutes,then you you need to convert dates into fixed decimal numbers and not the whole numbers. When I applied you M query(which works perfect with whole numbers) to the decimal numbers,it is not able to handle it. I tried to fiddle with your M query and tried different formats before [Date From]..[Date to] but couldnt succeed.
Could you please share the M query which can handle the dates in minutes? By that I mean the M query which could handle the decimal (maybe fixed decimals).
Regards