Forum Discussion

Microsoft Employee

10 years ago

Getting all dates between 2 dates

Hello guys, I have 2 columns named, start date and end date, I want to create a column which contains all the dates between the after mentioned dates, for example

Tips and Tricks

Microsoft Employee

10 years ago

Hi,

I reproduced you error. It happened because the data type of your “StartDate” and “EndDate” are Text, not Date.

Please change them to Date type then follow the steps below.

1, original table .

2. add a new column called “Custom” in query editor

3. after add the new column, expend the list

4. change the data type of “Custom” column to “Date”

Best Regards

Alex

inescosta
Advocate II
9 years ago
Hello,

I am trying to reproduce this solution but I get an error saying Number.from doesn't exist.
- ImkeF
  Community Champion
  9 years ago
  M is case sensitive. So it should be Number.From
  - inescosta
    Advocate II
    9 years ago
    Can I use M in the table itself or does it have to be in queryeditor? I ask because my table is already changed. the dates shown are from a related table, so those columns don't appear in queryeditor.
    Thank you
Senarath
Frequent Visitor
9 years ago
Dear AlexChen

I was looking for a similar solution like what you have posted. However, my data has values for each row and those are getting duplicated as the number of rows get duplicated for the date range. So I cannot get a sum of the valyue. e.g. Sum(Cost).

What do you reccommend for that please.

Thanks
Senarath
mbradley-gst
New Member
4 years ago
I've tried replicating this but get the error;
"Expression.Error: The number is out of range of a 32 bit integer value.
Details:
40800.99931"
The start and end date values are datetime rather than date. I've tried swapping them to date but this produces the same error, and ideally I would like to retain the time. Is there a way around this?

All the best,