How do I use a function on a table column in Query, when I want the inputted report to translate into a different number in Excel? Update: I need syntax help

Question

I know my title is confusing, it's the best I could do.

I am inputting an Excel report into Query, to organize it and lay it out on a spreadsheet. One of my values I want to show up as a duration, based on the date that came in on the report.

For example, if the report I upload to Query has the invoice dates, 8/18/24, 8/19/24, and 8/20/24, I want my spreadsheet to reflect that the invoices are 3, 2, and 1 days old.

I just can not figure out how to get Query to make the calculations, and I'm very out of practice in coding. Any ideas?

Answer 1

Hi @Marissa Austin

I suggest you try DATEDIFF function, like Days Duration = DATEDIFF([Invoice Date], TODAY(), DAY)

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

The use of DateTime.LocalNow requires a pair of opening and closing parenthesis (), similar to TODAY() in Excel. That the first error in the formula.

Then, Duration.TotalDays expects a number (not two date argements) from subtracting two dates, for example end_date - start_date. Both have to be of the Date data type. You can not subtract a Date from a DateTime.

Use this code in stead:

#"Invoice Date" = Int64.From(DateTime.LocalNow()) - Number.From([Invoice Date])

Answer 3

You forgot the pair of parenthesis after the function name.

Use DateTime.LocalNow() in stead.